Can I assign a sheetname to a variable?

[9tanstaafl9]

How would I assign an unknown sheetname to a variable so that I can go back to the worksheet again in my macro when I don't know what the name of the sheet will be (the sheet is being added during the macro?)

I was trying to do someting like this, but I keep getting a Runtime Error 9 subscript out of range, so I assume I'm making some sort of operator error... I'm really bad with variables, so I may have missed someting obvious.

Dim MySheet As String 
MySheet = ActiveSheet.Name
 
Range("Snapshot3").Select 
Application.Goto Reference:="Snapshot3"
Selection.SpecialCells(xlCellTypeVisible).Copy
Worksheets("MySheet").Range("p1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
Worksheets("MySheet").Range("P1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'it craps out on the worksheets("myshet"... line


all i'm really trying to do is get back to the newly added sheet, which was added with this line of code earlier in the macro:

    Application.Goto Reference:="Snapshot1"
    Selection.SpecialCells(xlCellTypeVisible).Copy
    ActiveWorkbook.Worksheets.Add After:=ActiveSheet

 

[Richard Schollar]

Use a worksheet variable to hold an object reference to the worksheet added, then it's easy:

Dim ws As Worksheet
 
Set ws = Worksheets.Add
 
'do other stuff:
'....
 
'go back to added worksheet:
 
ws.Range("A1").Value = "Hello"

 

[9tanstaafl9]

I'm sorry, and I thank you for your answer, but I'm afraid I haven't had quite enough coffee yet this morning to comprehend it. In your example, where is the worksheet name going? Is "hello" the new name of the worksheet?

In reality, I'm seriously do not want to rename the worksheet, I want to leave that what it was assigned naturally, I just want to grab the natural name, whatever it is, but be able to refer to it in my macro so that I can find the thing again and get back to it, just the one time. I'll NEVER need to get back to it again, even if the macro re-runs (since then a new sheet will be added and the name would be sheet +1...).

 

[9tanstaafl9]

Nevermind, I get it. The worksheet IS the variable. I'm slow. I didn't realize a worksheet could BE a variable itself. duh. sorry. Thank you. It didn't sink in before. I've never used variables for anything other than numbers or strings before. The concept didn't really sink in that I didn't need the name anymore.

 

[Richard Schollar]

I quite understand - I often think that coffee should be served in buckets in the morning!