plwhittington
Board Regular
- Joined
- May 10, 2007
- Messages
- 132
I have a worksheet (CurSht) that has date rangess listed in rows and I want to create new sheets with the date range being the new sheet name if that sheet does not exist already. I get a RUNTIME ERROR 9 - SUBSCRIPT OUT OF RANGE error when running the code when it gets to the SELECT statement to select the sheet. (all variables have valid values when the error occurs). I tried to use the ON ERROR GOTO command to create the sheet if it doesnt exist, but it ignores it. Not sure if I need a RESUME statement, but can't get it to work. Thanks for any suggestions. Below is the code.
For RowNum = 12 To LastRow
Sheets(CurSht).Select
.....
On Error GoTo DataShtDoesntExists:
Sheets(Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay).Select '******* RUNTIME ERROR 9 HERE
Sheets(Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay).Cells.Clear
GoTo DataShtAlreadyExists:
DataShtDoesntExists:
Sheets.Add.Name = Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay
DataShtAlreadyExists:
Next RowNum
For RowNum = 12 To LastRow
Sheets(CurSht).Select
.....
On Error GoTo DataShtDoesntExists:
Sheets(Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay).Select '******* RUNTIME ERROR 9 HERE
Sheets(Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay).Cells.Clear
GoTo DataShtAlreadyExists:
DataShtDoesntExists:
Sheets.Add.Name = Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay
DataShtAlreadyExists:
Next RowNum