CODEWITHSUNDEEP
excelvbaloopsworksheet
ChrisBaldock
ChrisBaldock

Reputation: 123

How do I loop through a list of worksheets that i have in a table?

I have 200 worksheets (named 1, 2, 3 and so on) that i need to loop through and copy and paste varying ranges of data from each one into a master worksheet. How can i do this?

I have a list of all the sheets in an array in the VB Editor and have tried to loop through it. I have a list of the sheet names in a table in another worksheet and have tried to loop through it as well.

Can someone provide a simple example of how one of these methods can be done?

Example code shown below. 

Dim i As Integer
i = 11
Dim SheetList As String
Dim element As Variant
SheetList = Array("Sheet24", "Sheet25")

'Simplified example of Do While loop being used
Do While Sheet1.Cells(i, 2).Value <> ""
    For Each element In SheetList
        If element.Range("C3") = "" Then
            GoTo Next Iteration
        Else
            'copy and paste data in master worksheet
        End If
NextIteration:

    i = i +1
    Next element
Loop

Expected result: ranges of data from a number of worksheets to be copy and pasted into one combined range on the master worksheet.

Upvotes: 0

Views: 1097

Answers (1)

Stavros Jon
Stavros Jon

Reputation: 1697

You can easily loop through all the worksheets in a workbook, without needing their names, using:

Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets

    'do something
Next sht

If the range you need to copy is different in every worksheet, then you could search for the headers of the columns you want to copy for example, get the range you need and paste it's contents to your master worksheet.

If there are specific sheets you want to use, then you could still loop through all the sheets and check if some condition is true for a specific sheet like so:

For Each sht In ThisWorkbook.Worksheets
    If sht.Name = "something" Then
        'do something
    End If
    If Condition Then
        'do something
    End If
Next sht

For example the sheets from which you want to copy some data, could have a "flag" cell with a specific value assigned to it, like TRUE or 1. The code would check for the contents of this cell and act accordingly.

Now if you want to save time by not looping through all the sheets (which shouldn't take too much time anyway..) and to avoid checking a condition for each sheet, then you could create a custom collection of worksheets, consisting of only those sheets you are interested in.

Dim sht As Worksheet
Dim shtcol As New Collection
shtcol.Add ThisWorkbook.Worksheets(1)
shtcol.Add ThisWorkbook.Worksheets(2)
shtcol.Add ThisWorkbook.Worksheets(3)
For Each sht In shtcol
    'do something
Next sht

Upvotes: 1

Related Questions