CODEWITHSUNDEEP
arraysvbaexcel
abdfahim
abdfahim

Reputation: 2553

Array of worksheet variable

As per documentation, I can define array of worksheets like below

Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))

But what happens if my worksheets are dynamically created and pointed by pointer variable like below? How can I make an array of them so that I can loop through them to do same activity?

Dim tempsheet1 As Worksheet
Dim tempsheet2 As Worksheet

Set tempsheet1 = Sheets.Add
Set tempsheet2 = Sheets.Add

Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array(tempsheet1, tempsheet2)) ' Doesn't work
For Each msheets In sheetsArray
    With msheets
        DO SOMETHING
    End With
Next msheets

Upvotes: 2

Views: 9268

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Since you should specify sheet names in Array, change

Set sheetsArray = ActiveWorkbook.Sheets(Array(tempsheet1, tempsheet2))

to 

Set sheetsArray = ActiveWorkbook.Sheets(Array(tempsheet1.Name, tempsheet2.Name))

Upvotes: 3

Related Questions