Solved Worksheet with numeric title

June 22, 2013 at 12:05:29
Specs: Windows 7
Hi there,
I have a small problem that I can not get around
I have a worksheet with the title '2013'
However when trying to paste to this worksheet using Vba,, I end up with a run time error.
If I change the title of the worksheet to 'Y2013', the paste takes place.
Can any one tell me why this happens and how to get around it without having to change the worksheet title

See More: Worksheet with numeric title

June 22, 2013 at 13:14:51
Without seeing your actual code, it's kind of hard to give a specific answer.

Without some more detail behind the error (there are many types of Run Time errors) it's kind of hard to give a specific answer.

Are you putting the 2013 in quotes within VBA? e.g.


If you used:


then VBA was looking for the 2013th sheet in the workbook, which I'm guessing doesn't exist.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 22, 2013 at 13:55:06
' Creates new sheet with title of stats year
    Dim ws As Worksheet
    Dim newSheetName As String
    newSheetName = Sheets("DASHBOARD").Range("Q1")
    For Each ws In Worksheets
    If ws.Name = newSheetName Or newSheetName = "" Then
    MsgBox "Sheet already exists or name is invalid", vbInformation
    Exit Sub
    End If
    Sheets.Add Type:="Worksheet"
    With ActiveSheet
    .Move After:=Worksheets(Worksheets.Count)
    .Name = newSheetName
    End With
' Pastes copy of current stats to correct sheet title
    ActiveWindow.SelectedSheets.Visible = False
    shtname = Sheets("DASHBOARD").Range("Q1")
    With Sheets(shtname).Range("A1")
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteColumnWidths
   End With

The Vba is failing at the line With Sheets(shtname).Range("A1")
If I change the text in Range("Q1") to read Y2013, the Vba works,
but not if the text reads 2013

Report •

June 22, 2013 at 14:19:39
✔ Best Answer
It's like I said in my previous post...with a twist:

If you have 2013 in Q1, the variable shtname is getting set to a number: 2013. Then when you use

With Sheets(shtname)

VBA is looking for Sheets(2013). Since you don't have 2013 sheets in your workbook, the code fails.

When you change Q1 to Y2013, a text string is returned and VBA is looking for Sheets("Y2013").

If you must use 2013, then you must tell VBA to use the text string "2013" not the number 2013:

shtname = Cstr(Sheets("DASHBOARD").Range("Q1"))

See here for other Type Conversion Functions:

BTW...You might find the following tutorial helpful. By using the techniques described, you can Single Step through your code and Watch the variables to see what they get set to. That can be very helpful when troubleshooting code. It's beats clicking Run over and over again and trying to figure how why the code fails each time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

June 22, 2013 at 14:43:28
Superb and thanks for the direction to the tutorials, very late here so will have a good look tomorrow

Report •

Ask Question