Assigning cell reference value

I've created a variable (newName) and assigned a string value representing a cell reference [Worksheets("Members").Range("B2")]. I've constructed it this way because I wish to interate the row/col reference to step through a name listing. I've assigned an Object variable (strName) to extract the value from the cell newName, but its not working!! What am I missing??

Roger

Hi

I don't really understand your question. Can you post your code?

Assigning cell reference value

Code herewith. I've stripped out a lot of extraneous code.
Option Explicit
' Registration Macro
' Construct a registration form for each member.
Sub CreateNewWorksheet()
'
    Dim Counter
    Dim Cellx, Cellx1, Cellx2, Cellx3, Cellx4
    Dim oSheet As Worksheet
    Dim strName As Object
    Dim newName2
    Dim newName
'
'   Construct Row/Col reference
'
    Counter = 2
    Cellx1 = "("""
    Cellx2 = "A"
    Cellx3 = Chr(34)
    Cellx4 = ")"
    Cellx = Cellx1 & Cellx2 & Counter & Cellx3 & Cellx4
'
'   Construct member name
'
    Set strName = Worksheets("Members")
'
    Worksheets("Members").Range("E2").Select
    newName = "Worksheets" & Cellx1 & "Members" & Cellx3 & Cellx4 & ".Range" & Cellx
    newName2 = newName
    strName = newName2    'This should produce "Chris" ??
    Set oSheet = Worksheets.Add(, strName)
    With oSheet
          .Name = strName
     End With
    End Sub

I see what you mean now...

You cannot assign it in this manner. I'm going to assume "Counter" will be something dynamic and changing. You should just do this: strName = Worksheets("Members").Range("A" & Counter) I would also declare strName as String instead of Object. So your code would be:
Option Explicit
' Registration Macro
' Construct a registration form for each member.
Sub CreateNewWorksheet()
'
Dim Counter
'Dim Cellx, Cellx1, Cellx2, Cellx3, Cellx4 'scrap this
Dim oSheet As Worksheet
'Dim strName As Object
Dim strName as String
'Dim newName2 'scrap this
'Dim newName 'scrap this
'
' Construct Row/Col reference
'
Counter = 2
'scrap this lot
'Cellx1 = "("""
'Cellx2 = "A"
'Cellx3 = Chr(34)
'Cellx4 = ")"
'Cellx = Cellx1 & Cellx2 & Counter & Cellx3 & Cellx4
'
' Construct member name
'
Set strName = Worksheets("Members")
'
Worksheets("Members").Range("E2").Select
'newName = "Worksheets" & Cellx1 & "Members" & Cellx3 & Cellx4 & ".Range" & Cellx
'newName2 = newName
'strName = newName2 'This should produce "Chris" ??
strName = Worksheets("Members").Range("A" & Counter)
 
'Set oSheet = Worksheets.Add(, strName) 'scrap this, it won't work because you want to add it after the "Members" sheet as I recall.
Set oSheet = Worksheets.Add(, Worksheets("Members"))
With oSheet
.Name = strName
End With
End Sub