Clear used range from array of sheets

[FryGirl]

I have an array of worksheets that need to have the used ranges cleared. This works fine, but now can I use it when you use a variable and Set the names of the worksheets?

This does not work for me -- Dim ShtArr: ShtArr = Array("wsone", "wstwo", "wsthree", "wsfour")

Sub ClearSheets()
    
    Dim wsone      As Worksheet: Set wsone = Sheets("Inventory")
    Dim wstwo      As Worksheet: Set wstwo = Sheets("Sales")
    Dim wsthree    As Worksheet: Set wsthree = Sheets("Work Hours")
    Dim wsfour     As Worksheet: Set wsfour = Sheets("Misc")

    Dim ShtArr: ShtArr = Array("Inventory", "Sales", "Work Hours", "Misc")
    Dim ShtArrCounter As Long
    For ShtArrCounter = 0 To UBound(ShtArr): Sheets(ShtArr(ShtArrCounter)).UsedRange.Clear: Next
    
End Sub

 

[rollis13]

Tested it, does work except for locked sheets. What error does it display and where.
Or do you mean this:

'...
Dim ShtArr: ShtArr = Array(wsone, wstwo, wsthree, wsfour)
Dim ShtArrCounter As Long
For ShtArrCounter = 0 To UBound(ShtArr): Sheets(ShtArr(ShtArrCounter).Name).UsedRange.Clear: Next

 

[FryGirl]

That does it. So it's the .Name piece that was needed. Thank you

 

[rollis13]

Not only, you needed to change your Array .
Also, you could have done some debugging to see what was 'passed' by ShtArr(ShtArrCounter) when using a variable in the array.
Thanks for the positive feedback, glad having been of some help.

 

[Fluff]

As you have declared the variables as worksheets, you could also use

    Dim ShtArr: ShtArr = Array(wsone, wstwo, wsthree, wsfour)
    Dim ShtArrCounter As Long
    For ShtArrCounter = 0 To UBound(ShtArr): ShtArr(ShtArrCounter).UsedRange.Clear: Next

 

[FryGirl]

Thank you Fluff for the additional help. In the end, I'm thinking about using the below which kind of cuts out the middleman.

I'll just change the code name in the VBE that way it doesn't matter what is the name on the worksheet tab. Eliminates potential error I suppose.

Sub ClearSheets()
    Dim ShtArrCounter As Long
    Dim ShtArr: ShtArr = Array(Inventory, Sales, WorkHours, Misc)
    For ShtArrCounter = 0 To UBound(ShtArr): ShtArr(ShtArrCounter).UsedRange.Clear: Next ShtArrCounter
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.