Reference a worksheet via a variable

Question

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.

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
– Scott Craner, Commented Sep 9, 2015 at 16:34

Excel Hero · Accepted Answer · 2015-09-09 16:47:29Z

1

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

answered Sep 9, 2015 at 16:47

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 9, 2015 at 16:55

Ambie

4,9872 gold badges16 silver badges30 bronze badges

3 Comments

Excel Hero Over a year ago

Each worksheet in a workbook is already a member of two built-in collections: Sheets and Worksheets. What would be the purpose of overtly adding them to another collection with regards to this question?

Ambie Over a year ago

I don't think the Op wants to iterate through the Worksheets collection of the Workbook object (ie every worksheet sheet). I think he wants to iterate through a list he's generated of specific worksheets (ie only some sheets). This answer was meant to show him a way of adding just those worksheets to his own iterable collection.

Werdna Over a year ago

Thanks All. Second solution fits my need better (as i only want to clear certain sheets).

Collectives™ on Stack Overflow

Reference a worksheet via a variable

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related