Next Sheet Loop

[English Accountant]

I am trying to clear nearly all the tabs on a spreadsheet, but when it gets to the last tab it I get the error message "Object variable or With block variable not set".

I am struggling to find a comparable example so any help would be gratefully received!

This is my VBA code:

Clear entire workbook
'
' Keyboard Shortcut:
'
Dim ws As Worksheet

Application.Goto Reference:="ClearAll"

For Each ws In ActiveWorkbook.Worksheets

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

     'ActiveSheet.ShowAllData

   
    Range("A16:E215").Select
    Selection.ClearContents
    Range("G16:H215").Select
    Selection.ClearContents
    Range("K16:M215").Select
    Selection.ClearContents
    Range("A16").Select

ActiveSheet.Next.Select

Next

End Sub

 

[nemmi69]

is this what you wanted?

Sub XX()
'
' Keyboard Shortcut:
'
Dim Ws As Worksheet

Application.Goto Reference:="ClearAll"

For Each Ws In ActiveWorkbook.Worksheets

    If (Ws.AutoFilterMode And Ws.FilterMode) Or Ws.FilterMode Then
        Ws.ShowAllData
    End If
    
    'ActiveSheet.ShowAllData
    
    
    Ws.Range("A16:E215").ClearContents
    Ws.Range("G16:H215").ClearContents
    Ws.Range("K16:M215").ClearContents
    
    'ActiveSheet.Next.Select
    
Next

End Sub

 

[English Accountant]

Thanks for your reply but no as it should clear one tab and then move to the next tab then clear and the next until the 53rd tab then end but it comes up with the error message.

 

[Joe4]

What exactly is on that tab that it is erroring on?
Do you have any errors on that tab, or perhaps some protected ranges or merged cells?

 

[English Accountant]

I think it is closing the "loop" because it is looking for a "next" tab that doesn't exist. "Activesheet.Next.Select" which I to clear down nearly all of the tabs.

 

[Joe4]

I think it is closing the "loop" because it is looking for a "next" tab that doesn't exist. "Activesheet.Next.Select" which I to clear down nearly all of the tabs.

Did you notice that Nemmi commented that line out of the code (commenting a line of code is the same thing as removing it altogether - it is not active)?

By prefacing all the ranges with the "ws" worksheet reference, like Nemmi did, their is no need to change the ActiveSheet.

 

[English Accountant]

Thanks, nearly there but there are two tabs that I don't want clearing the ranges specified down and this clears all tabs including information i need to keep.

Apologies, I'm not the best at VBA!

 

[Joe4]

Thanks, nearly there but there are two tabs that I don't want clearing the ranges specified down and this clears all tabs including information i need to keep.

Apologies, I'm not the best at VBA!

OK, that is an important detail you failed to mention before now.

You can simply imbed the ClearContents lines in an IF block that checks to make sure that you are not in one of those two sheets:

Sub XX()
'
' Keyboard Shortcut:
'
Dim Ws As Worksheet

Application.Goto Reference:="ClearAll"

For Each Ws In ActiveWorkbook.Worksheets

    If (Ws.AutoFilterMode And Ws.FilterMode) Or Ws.FilterMode Then
        Ws.ShowAllData
    End If
   
    If (Ws.Name <> "Sheet1") And (Ws.Name <> "Sheet2") Then
        Ws.Range("A16:E215").ClearContents
        Ws.Range("G16:H215").ClearContents
        Ws.Range("K16:M215").ClearContents
    End If
   
Next Ws

End Sub

Just change "Sheet1" and "Sheet2" with the actual names of your two sheets you want excluded from this clearning.

 

[English Accountant]

Thanks! That works now.

Sorry, I put "Nearly All Tabs" in my original message but it wasn't clear.

 

[Joe4]

You are welcome.
Glad we were able to help and you were able to get code that does what you need.

Typically, you want to try to include as all the pertinent information in your initial question, if possible. That will increase the chances of you getting an answer that works for you quicker (and with less "back-and-forth").

Worst case scenarios, I have seen people drop big bomb shells that change the whole nature of the question in follow-up posts. Sometimes that backfires because the original responder may not know how to help with the expanded question (and now the question no longer shows up on the "Unanswered threads" list, meaning it will get less people seeing it). Or sometimes people oversimplify the question, which can also be a mistake too, because you may get answers that answer the question you asked, but don't actually work for your real problem.

So I usually advise people that to get the best possible help, try to be as detailed as possible in your original post, and don't oversimplify things (unless you are really confident that you can take a simplified response and expand it to what you need yourself).

Just some tips for getting the most out of our board.
Happy Excel-ling!