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

Question

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

Shai Rado · Accepted Answer · 2017-12-19T10:06:29.353

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

Thanks for your suggestion I will test it immediatly – Tim Dec 19 '17 at 10:13 — Tim, Dec 19 '17 at 10:13
@Tim any feedbacks ? is it working as you intended ? – Shai Rado Dec 19 '17 at 11:12 — Shai Rado, Dec 19 '17 at 11:12

Vityata · Answer 2 · 2017-12-19T10:00:12.700

0

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:

avoid using _ in variable names in VBA, some people hate it.
Why Use Integer Instead of Long?

edited Dec 19 '17 at 10:00

answered Dec 19 '17 at 09:57

Vityata

42,633
8
55
100

thanks for the suggestion if I can make my problem more clear. I was not able to create a working loop that loops through a list of worksheets and performs the second For loop in my macro. I will test your solution right now. – Tim Dec 19 '17 at 09:59
1

Thank you for your useful tips, I implemented your solution and the macro does run but after a while I get an error saying "Subscript out of range" and the macro also takes a really long time to run Im guessing because of the large amounts of data it has to check. Is there any way to make this run smoother? – Tim Dec 19 '17 at 10:12
@Tim - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Vityata Dec 19 '17 at 10:14
@Tim - are you sure that all the sheets in `L21:L49` are present in the Workbook? Without spaces or anything? At which sheet you get this error? Is it present in the workbook? Does it have space or anything? Why do I feel like the guys that are writing questions in English textbooks? – Vityata Dec 19 '17 at 10:16
1

Haha I feel the stress of getting questioned in class for sure.. I solved the issue in the mean time it was as always human error of misspelling. Thank you sir. – Tim Dec 19 '17 at 10:27

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

2 Answers2