Can I assign a sheetname to a variable?

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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.

Code:
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:

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

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Use a worksheet variable to hold an object reference to the worksheet added, then it's easy:

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

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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

Well-known Member
Joined
Mar 23, 2008
Messages
535
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,228
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top