CODEWITHSUNDEEP
excelvba
MrPontes
MrPontes

Reputation: 49

Setting worksheets dynamically in VBA

I have 66 sheets in my document (Sheet1, Sheet2, Sheet3, ...) and I'm trying to access all of them with the following code:

Dim currentSheet As Worksheet
For i = 1 To 66
    Set currentSheet = "Sheet" & Cstr(i)
Next i

However, I get the "type mismatch" error. It seems I cannot set a worksheet using a string. How can I make this work?

Upvotes: 0

Views: 1333

Answers (3)

IndieMamba
IndieMamba

Reputation: 73

I would rather say you do it this way:

Sub LoopThroughWorksheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim wsTarget As Worksheet

'You can set the workbook in which the sheets are there here, if not the current workbook
Set wb = ThisWorkbook

'Loop through each sheet in the current workbook
For Each ws In wb.Sheets
    
    'Set the current ws as the target worksheet
    Set wsTarget = ws
    
    'You can then pass this wsTarget worksheet object to other function/sub

'Next worksheet
Next ws

End Sub

Another alternate way:

Sub LoopThroughWorksheetsMethod2()

Dim wb As Workbook
Dim intWs As Integer
Dim wsTarget As Worksheet

'You can set the workbook in which the sheets are there here, if not the current workbook
Set wb = ThisWorkbook

'Loop through all the sheets in the current workbook
For intWs = 1 To wb.Sheets.Count
    
    'Set the current ws as the target worksheet
    Set wsTarget = wb.Sheets(intWs)
    
    'You can then pass this wsTarget worksheet object to other function/sub

'Next worksheet count
Next intWs

End Sub

By this you'll be creating a dynamic solution, in case your worksheet count changes in future.

Upvotes: 1

ArcherBird
ArcherBird

Reputation: 2134

You can access the worksheet via the Workbook's worksheets collection.

Dim currentSheet As Worksheet

For i = 1 To 66
    Set currentSheet = ThisWorkbook.Worksheets("Sheet" i)
    '// do stuff with currentSheet...
Next i

Important things here are that this assumes your sheets bear the default names "Sheet" followed by a sequential number. If the loop arrives at "Sheet 33" and it doesn't exist, you will get an error.

If you want to cycle through ALL sheets, you could do it more safely like this:

Dim currentSheet As Worksheet
Dim wsName as String

For Each currentSheet in ThisWorkbook.Worksheets
    '// do stuff with currentSheet... for example...
    wsName = currentSheet.Name
Next i

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

You can retrieve the worksheet as a dynamic array.

Sub test()
    Dim vWs() As Worksheet
    Dim Ws As Worksheet
    Dim n As Integer
    For Each Ws In Worksheets
        n = n + 1
        ReDim Preserve vWs(1 To n)
        Set vWs(n) = Ws
        Debug.Print vWs(n).Name
    Next Ws
    
End Sub

Upvotes: 0

Related Questions