'ON ERROR GOTO' not working inside '-FOR NEXT' loop

[plwhittington]

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

 

[Puertorekinsam]

Where are your variables being defined? Yr Mnth BegDay EndDay? Are these in columns?

 

[Puertorekinsam]

try something like this :
obviously you need to define your variables

    Sub sheetname_Array()
    
    Dim ws As Worksheet
    Dim SheetsArray() As String
    ReDim SheetsArray(0)
    For Each ws In ActiveWorkbook.Worksheets
        SheetsArray(UBound(SheetsArray)) = ws.Name
        ReDim Preserve SheetsArray(1 + UBound(SheetsArray))
        
    Next
    ' I believe SheetToLookFor is Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay
    SheetToLookFor = Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay
    If IsInArray = (UBound(Filter(SheetsArray, SheetToLookFor)) > -1) Then
        Sheets(Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay).Cells.Clear
    Else
        Sheets.Add.Name = Yr & Mnth & BegDay & "-" & Yr & Mnth & EndDay
    End If


End Sub

 

[JoeMo]

From the limited portion of code you've posted, it's not clear why your GoTo isn't working. As a test, place this line just before the GoTo line:

MsgBox Err.Number

what do you get in the message box when you run the code?

 

[dmt32]

Try removing colon marks shown in RED

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:

Dave

 

[plwhittington]

Puertorekinsam - the variables are defiuned in the columns. I took the code you provided was able to accomplish what I needed. Thank you for your help.

JoeMo- the error code was 9

DMT32- tried that solution first, but still encountered the error. Thanks anyway for the suggestion.

 

[JoeMo]

Puertorekinsam - the variables are defiuned in the columns. I took the code you provided was able to accomplish what I needed. Thank you for your help.

JoeMo- the error code was 9

DMT32- tried that solution first, but still encountered the error. Thanks anyway for the suggestion.

That's your problem, you already have an error in your code coming into the GoTo. You need to deal with that error (i.e clear it) before you execute the GoTo line or, as you have observed, the GoTo line will be ignored. You might want to post all of your code if you have problems correcting that.