"Subscript out of range" -- using variable as new name of workshee

[Eric_G]

I am attempting to use a variable which I've declared in the DECLARATIONS
section as the new name of a worksheet.

Public period_end As String

Public Sub Run_Monthly_Update()
period_end = InputBox("Enter reporting month-end in MMM-YYYY format")
......
ChDir "C:\List Security Downloads\"
Workbooks.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = period_end

AND that's where the macro bombs.... I get the "subsript out of range"
error. I've confirmed that the value of "period_end" is, in fact, the value
I input in response to the inputbox message by going to the "immediate" box.
For some reason, I can't re-name the worksheet in the new workbook.

Any suggestions?

 

[jasontferrell]

It looks like you could be getting the Subscript out of Range not on
the rename line but on the Sheets("Sheet1").Select line. You can get
this error if it's not a valid sheet.
Using a global reference like "Sheets("Sheet1").Select" without
telling Excel which workbook to use can be tricky. A more robust way
to write it would be:

Dim wkb As Workbook
period_end = InputBox("Enter reporting month-end in MMM-YYYY format")
Set wkb = Workbooks.Add
wkb.Sheets(1).Name = period_end

This way you don't have to worry about what the new sheet name will be
(it just uses the first sheet in the new workbook). And there's no
confusion about which workbook you want to use.

 

[Eric_G]

That did the trick -- many thanks.