Could you please help me to write a macro for this situation?

[dener123]

I have a sheet name "Data", "Jan", "Feb", "Mar", "Apr".

I made a macro that inputs Jan ~ Dec sheet's visitors data into "Data" sheet.
I do not have May ~ Dec sheets yet.
So when I ran the macro to input the data, it shows a pop up saying;

Run-time error `9`
Subscript out of range

I know this is showing because I do not have the actual sheet for May ~ Dec yet, but could you please write a macro that does something like, "If the sheet doesn't exist, skip and move to next" ?

I want to keep this macro that analyzes from Jan ~ Dec, but doesn't want to show that pop up when it is missing certain month's sheet and just skips to next.

 

[Micron]

If you have code it's a good idea to post it to give anyone a starting point. When posting code, please paste within code tags (use VBA button on forum posting toolbar) to maintain indentation and readability. What you probably want to do is create a counter (For i = 1 to x) where x is the count of sheets in your workbook. That way, the counter increases as you add sheets. This may not work if you have sheets within that count that you don't want the code to affect, but it should be possible to ignore those.

Doing as you suggest is a possible approach, buy it's always better to prevent an error rather that work around it, especially when you know it's going to occur.

 

[dener123]

If you have code it's a good idea to post it to give anyone a starting point. When posting code, please paste within code tags (use VBA button on forum posting toolbar) to maintain indentation and readability. What you probably want to do is create a counter (For i = 1 to x) where x is the count of sheets in your workbook. That way, the counter increases as you add sheets. This may not work if you have sheets within that count that you don't want the code to affect, but it should be possible to ignore those.

Doing as you suggest is a possible approach, buy it's always better to prevent an error rather that work around it, especially when you know it's going to occur.

I have the following code that deletes Sheets "Jan" all the way to "Dec".
However, I do not have sheets from "Jul" to "Dec" yet because I do not have the data.
When I run this code, it pops up a bug because it does not have sheets from Jul to Dec. I do not want this pop up.

I need the code for the scenario; If the following sheet does not exist, skip it and move to next command"

Sheets("Jan").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Feb").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Mar").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Apr").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("May").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Jun").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Jul").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Aug").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sep").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Oct").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Nov").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Dec").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

I have a sheet name "Data", "Jan", "Feb", "Mar", "Apr".

I made a macro that inputs Jan ~ Dec sheet's visitors data into "Data" sheet.
I do not have May ~ Dec sheets yet.
So when I ran the macro to input the data, it shows a pop up saying;

Run-time error `9`
Subscript out of range

I know this is showing because I do not have the actual sheet for May ~ Dec yet, but could you please write a macro that does something like, "If the sheet doesn't exist, skip and move to next" ?

I want to keep this macro that analyzes from Jan ~ Dec, but doesn't want to show that pop up when it is missing certain month's sheet and just skips to next.

 

[Micron]

When posting code, please paste within code tags (use VBA button on forum posting toolbar) to maintain indentation and readability.

Sorry, not going to read that, especially when I asked you to do otherwise. I have trouble compartmentalizing things when code is posted like that, so no.
Repeating your previous statements is not necessary.

I made a macro that inputs Jan ~ Dec sheet's visitors data into "Data" sheet.

Except that you posted code that deletes sheets.

I have the following code that deletes Sheets "Jan" all the way to "Dec".

Those are 2 entirely different explanations of what you have. Which is it?

 

[dener123]

Sorry, not going to read that, especially when I asked you to do otherwise. I have trouble compartmentalizing things when code is posted like that, so no.
Repeating your previous statements is not necessary.


Except that you posted code that deletes sheets.

Those are 2 entirely different explanations of what you have. Which is it?

Sorry for confusion.
What I'm trying to say is that;

Sheets("Jan").Select <- If this sheet ("Jan") does not exist, move to the next macro ("Feb")
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Feb").Select <- If this sheet ("Feb") does not exist, move to the next macro ("Mar").... and so on until ("Dec")
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Mar").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

 

[Joe4]

Check to see if the sheet exists first before trying to select it.
You can borrow the code shown in this thread which shows you how to do that:

VBA check if sheet exists, if yes: select. If no select another sheet name

I need help with a code that can determine if a sheet name exists and do the following: If exists: select the sheet and run a certain code If it does not exist, select another sheet name and run a different code Help with this code would be greatly appreciated. Thank you

www.mrexcel.com

 

[jolivanes]

If it does not exist, add monthly sheet.

Sub All_Months()
Dim mo, ws As Worksheet, i As Long, csh As Worksheet
Set csh = ActiveSheet
mo = Application.GetCustomListContents(3)
Application.ScreenUpdating = False
    For i = LBound(mo, 1) To UBound(mo, 1)
    On Error Resume Next
        Set ws = Worksheets(mo(i))
    On Error GoTo 0
    If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = mo(i)
    End If
    Set ws = Nothing
    Next i
csh.Select
Application.ScreenUpdating = True
End Sub

If it exists, delete monthly sheets

Sub Delete_All_Months()
Dim mo, ws As Worksheet, i As Long
mo = Application.GetCustomListContents(3)
Application.ScreenUpdating = False
    For i = LBound(mo, 1) To UBound(mo, 1)
    On Error Resume Next
        Set ws = Worksheets(mo(i))
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
            Worksheets(mo(i)).Delete
        Application.DisplayAlerts = True
    End If
    Set ws = Nothing
    Next i
Application.ScreenUpdating = True
End Sub