Referring to Sheet Name as Variable gives "Subscript Out of Range" Error

[soccerkingpilot]

Today's not my day :P Here's post number 2...I've created an error handler to create a worksheet if it doesn't exist. It creates the spreadsheet and names it after the year of a date that I've captured. On the line I've marked below, it gives me Run-time Error 9. I know it's catching on the Sheets(ShtName) because I've tested it without everything else and it still snags. I don't understand why it won't find the Sheet named (in this case) 2011 when it used the same variable to create the sheet named 2011.

errHandler:On Error Resume Next
If wsTest Is Nothing Then
    Set AddSheet = MnS.Worksheets.Add
    ShtName = Year(FADate)
    AddSheet.Name = ShtName
    Set dest = Sheets(ShtName).Range("A1:AZ1")  '<---Error happens here
    Sheets("2012").Range("A1:AZ1").Copy dest
    Cells(1, 1).PasteSpecial
End If

Thanks,
Austin

 

[Al Chara]

Replace the line of code with, Set dest = AddSheet.Range("A1:AZ1")

I also had to remove, Cells(1, 1).PasteSpecial, as this will also cause an error.

 

[soccerkingpilot]

I also had to remove, Cells(1, 1).PasteSpecial, as this will also cause an error.

Oops, forgot to remove that..I knew that bit didn't work. Thanks, I'll try that out later today.

 

[soccerkingpilot]

I figured out the problem. When I used Year(FADate), it kept a leading space on the year. So ShtName was actually " 2011". Naturally, Excel removed the leading space when I created the sheet because a sheet name can't start with a space. That's why it didn't think the sheet existed. It was comparing "2011" with " 2011". Thanks for the help.

-Austin