Refering to a Sheet Name in a macro

[tedd13]

I have a workbook with one sheet, that I will call the "master sheet". It
contains all my data. I am selecting a row from this master sheet, opening a
new sheet, and pasting my data from the master sheet to this new sheet.

I then take the value of a cell in the new sheet and use that value for the
sheet name. For example, a cell may contain the value '50'. So '50' becomes
the sheet name. I use this code:
Dim sheetName
sheetName = ActiveCell.Value
Sheets("Sheet1").Select
Sheets("Sheet1").Name = sheetName

This works. I then go back to the master sheet and select another row.
What I want to do then is to go back to the sheet I just created by using the
variable 'sheetName' (this name may change, that's why I want to use the
variable).

I tried using something like: Sheets 'sheetName'.Select or Sheets
(sheetName).Select. But it errors out because of sheetName. When I mouse
over sheetName in the macro it gives me the value of the new sheet. So I
know it has the correct value in it.

Is there a way of doing this? Thanks.

 

[Tom Ogilvy]

sheets(sheetname).Select

should work if sheetname is a variable that contains the name of a sheet in
the activeworkbook.

Here is a demonstration from the immediate window:
sheetname = "Sheet2"
? activesheet.Name
Sheet1 (2)
sheets(sheetname).Select
?Activesheet.Name
Sheet2

 

[a7n9]

Even though your sheetname variable has a number, it is not a number
when you refer it.

Try
Dim sheetName
sheetName = CStr(ActiveCell.Value)
Sheets("Sheet1").Name = sheetName

Also, you don't have to select a sheet to rename it, you can directly
do it.

 

[Tom Ogilvy]

a7n9 raises a good point. sheetname should be typed as string.

Sub efg()
Dim sheetname As String
sheetname = 50
Sheets(sheetname).Select

End Sub

works fine, but if you made sheetname a variant either by declaration or by
not declaring it at all (variant is the default), then it would contain the
number 50 and look for the 50th sheet in the sheet tab - which would raise an
error.