Trying to put a loop in a macro to clear certain worksheets

[sspatriots]

As the thread title suggest, I cannot get a small piece of code to loop through some specific worksheets in a workbook to delete the entire contents on each worksheet (including tables). Below is what I have, but no matter what I do to it, it doesn't loop to the next worksheet. I'm guessing there is some simple principle that I'm missing here, but can't find it. Any help is much appreciated. Thanks, Steve

Dim Sht As Variant
Dim Shts As Variant

Shts = Array("2023", "2022", "2021", "2020", "2019")

For Each Sht In Shts    'Loop Sheets Code goes here:
    Cells.Select
    Selection.ClearContents
Next Sht

 

[johnnyL]

Try something like the following:

    Dim Sht As Variant
    Dim Shts As Variant
'
    Shts = Array("2023", "2022", "2021", "2020", "2019")
'
    For Each Sht In Shts    'Loop Sheets Code goes here:
        On Error Resume Next
            Sheets(Sht).Cells.ClearContents
        On Error GoTo 0
    Next

 

[kevin9999]

Or possibly...

Dim ws As Worksheet
For Each ws In Sheets(Array("2023", "2022", "2021", "2020", "2019"))
    ws.Cells.ClearContents
Next ws

 

[johnnyL]

LTNS kelvin. What happens if the sheet name in the array doesn't exist?

 

[johnnyL]

But now that you have me look at the code that I originally supplied, I shorten my answer to:

    Dim Sht As Variant
'
    For Each Sht In Array("2023", "2022", "2021", "2020", "2019")    'Loop Sheets Code goes here:
        On Error Resume Next
            Sheets(Sht).Cells.ClearContents
        On Error GoTo 0
    Next

 

[aqi0611]

 

[vcoolio]

Another option:

Option Explicit
Sub Test()

    Dim ar As Variant, i As Long
    ar = Array("2023", "2022", "2021", "2020", "2019")
    
    For i = 0 To UBound(ar)
          On Error Resume Next '---->Just in case a sheet no longer exists.
               Sheets(ar(i)).Cells.Clear
          On Error GoTo 0
    Next i

End Sub

I hope that this helps.
Cheerio,
vcoolio.

 

[sspatriots]

But now that you have me look at the code that I originally supplied, I shorten my answer to:

    Dim Sht As Variant
'
    For Each Sht In Array("2023", "2022", "2021", "2020", "2019")    'Loop Sheets Code goes here:
        On Error Resume Next
            Sheets(Sht).Cells.ClearContents
        On Error GoTo 0
    Next

I ended up using this code because it was much like other code I had elsewhere in my workbook. I just couldn't figure out how to make it work. Thank you.

 

[sspatriots]

Thanks for all the suggestions. I ended up going with the solution in Post #5 because of the similarities with some code I already had. Thanks again, SS

 

[johnnyL]

Glad we could help.