Loop through a list of Worksheets and copy paste data into summary sheet

1
votes

Hello guys I am trying to loop through a list of specific worksheets that are named in a certain range and then copy paste data from those sheets into a summary sheet.

So far I have this code:

Sub MacroToDoTheWork()

Dim ws As Worksheet
Dim ZeileUntersucht As Integer
Dim ZeileEintragen As Integer
Dim sheet_name As Range


ZeileUntersucht = 17  
ZeileEintragen = 2

For each sheet_name in Sheets("Frontend").Range("L21:L49")

 For ZeileUntersucht = 20 To 515
    If ws.Cells(ZeileUntersucht, 238).Value = "yes" Then
        Worksheets("Market Place Output").Cells(ZeileEintragen, 1) = ws.Cells(ZeileUntersucht, 1)
        ZeileEintragen = ZeileEintragen + 1

    End If
Next ZeileUntersucht

Next sheet_name

End Sub

The For loop is working and goes through the selected sheets range to check for a criteria and pastes the values into another sheet. What I am having issues with is the For each loop. Getting this loop to work for a list of worksheets. The Frontend Range L21:L49 is the range where the worksheet names are stored.

If you need further information, please ask

2
vbaexcel

2 Answers

1
votes

You can read all your sheet names from the Range to sheet_names array.

Later, when looping through all Sheets in ThisWorkbook, you can check if current sheet in the loop matches one of the names in the array using the Match function.

Note: if you try to do it the other way, looping through the sheet names in your Sheets("Frontend").Range("L21:L49") , and then use that name of the sheet, you can get a run-time error, if the sheet name won;t be found in any of the sheets in your workbook.

Modified Code

Dim Sht As Worksheet
Dim sheet_names As Variant

' getting the sheet names inside an array
sheet_names = Application.Transpose(Sheets("Frontend").Range("L21:L49").Value)

' loop through worksheets
For Each Sht In ThisWorkbook.Sheets
    ' use Macth function to check if current sheet's name matches one of the sheets in your Range
    If Not IsError(Application.Match(Sht.Name, sheet_names, 0)) Then
        ' do here your copy Paste


    End If

Next Sht
0
votes

I did not understand you problem exactly, but I suppose it would be fixed, if you try it like this:

For Each sheet_name In Sheets("Frontend").Range("L21:L49")
    Set ws = Worksheets(sheet_name.Text)
    For ZeileUntersucht = 20 To 515
        If ws.Cells(ZeileUntersucht, 238).Value = "yes" Then
            Worksheets("Market Place Output").Cells(ZeileEintragen, 1) = ws.Cells(ZeileUntersucht, 1)
            ZeileEintragen = ZeileEintragen + 1
        End If
    Next ZeileUntersucht
Next sheet_name

If your idea is that the sheet_name is the name of the worksheet, then it should work.

Two ideas: