Reference a worksheet via a variable

0
votes

I want to reference Sheets within a Workbook via an index. - essentially as I want to run through a loop and clear certain cells on several worksheets.

At present I've written some vba which includes code like:

Sheet36.Activate Range("B3:T201").ClearContents

repeated each time for each sheet, for instance: Sheet37.Activate Range("B3:T201").ClearContents

All answers appreciated.

2
excelvbavariables
So, you want to loop through each sheet and then use an if statement or case select statement to determine what to clear on any given sheet? – Scott Craner

2 Answers

1
votes

If you have just a few sheets that you wish to clear a range on, it's easy to reference them all at the same time like so:

Sheets(Array("Sheet36", "Sheet37").Range("B3:T201").ClearContents

But if you really want to loop all of the sheets, then this is how:

Dim sh
For Each sh In Worksheets
    sh.Range("B3:T201").ClearContents
Next
0
votes

Clarification added thanks to ExcelHero.

You could use a Collection of the specific worksheets you are interested in. Something like:

Private mChosenSheets As Collection

Sub Init()
    Set mChosenSheets = New Collection
    
    ' Add here whichever worksheets you want in your iterable list
    mChosenSheets.Add Sheet6
    mChosenSheets.Add Sheet36
    mChosenSheets.Add Sheet38
    ' etc...
    
End Sub

Sub ClearChosenSheets()
    Dim sht As Worksheet
    
    For Each sht In mChosenSheets
        sht.Range("B3:T201").ClearContents
    Next
    
End Sub

The Workbook.Worksheets collection can be used if you just want to iterate through every worksheet in the workbook.