Loop through worksheets to consolidate data

Question

I've got a basic understanding of VBA and usually just google my way out when i'm stuck, but this time I have looked around and can't seem to find a solution for my problem.

I've got a 'source' workbook which contains multiple sheets. Each month the workbook name changes and so do the worksheet names. I'm trying to consolidate the data from each of those worksheets into a 'Summary' workbook. there is some usless cells, so i only require some specific data. The number of rows in the 'source' worksheets varies, but the columns stay the same. The source worksheets have a single date at the top of sheet, which i want to insert a column in A:A and then filldown the date for each row entry.

I've tried both the 'For each ws in worksheets' and the 'for i = 1 to sheet count' methods and what ends up happening is that it copies data from only 1 sheet, but it repeats it multiple times, my guess is for how many sheets are in the workbook.

Really appreciate any help you can give. Thanks in advance. I don't know too much about coding so would appreciate any tips.


Private Sub CopyfromSheet()
    
    Dim ws As Worksheet
             
    Workbooks("source.xls").Activate

For each ws in  Workbooks("source").Worksheets
    
'add date column and copy date
    Columns(1).Insert   
    Range("D2").Copy
    Range("A3").Select
    Selection.PasteSpecial xlPasteValues

'fill down date to the last row    
    Range("B3").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown

'copy source data to next free row in Summary sheet   
    Range("A4:E4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    ThisWorkbook.Activate
    Worksheets("Data").Range("A2").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Workbooks("source").Activate

next ws   

End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 14 '20 at 07:33

Damian · Accepted Answer · 2020-09-14T08:30:30.963

Like Pᴇʜ stated, the problem here is that you are always using the ActiveSheet and never changing it. One way to solve your problem would be just ws.Activate right after the For Each, but a better way would be this:

Option Explicit
Private Sub CopyfromSheet()
    
    'Declare a worksheet variable to loop through the
    'source workbook sheets
    Dim ws As Worksheet
    'Declare a Long variable to get the last row with data
    Dim LastRow As Long
    'Declare A Date Variable to store the date
    Dim MyDate As Date
    'Declare a Range variable to store your data
    Dim RangeToCopy As Range
    
    For Each ws In Workbooks("source").Worksheets
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        MyDate = ws.Range("D2")
        Set RangeToCopy = ws.Range("A4:D" & LastRow)
        'Pass the Date and Range variables to an external
        'procedure to do the paste
        PasteDataFromSheet MyDate, RangeToCopy
    Next ws

End Sub
Private Sub PasteDataFromSheet(MyDate As Date, RangeToCopy As Range)
    
    'Use a With block to refer to the object in case
    'just by using a Dot
    With ThisWorkbook.Sheets("Data")
        'Get the first row without data
        Dim LastRow As Long
        LastRow = .Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
        
        'Paste the Date to the new rows
        .Range("A" & LastRow).Resize(RangeToCopy.Rows.Count).Value = MyDate
        
        'Paste the data from the Range variable
        .Range("B" & LastRow).Resize(RangeToCopy.Rows.Count, _
                                    RangeToCopy.Columns.Count).Value = _
                                    RangeToCopy.Value
    
    End With

End Sub

Note that Option Explicit forces you to declare all your variables.

This is fantastic! Thank you so much for your help Damian, I really do appreciate it. I had a feeling it was something to do with my use of selecting sheets and object variables. — robert021478, Sep 15 '20 at 05:14

Loop through worksheets to consolidate data

1 Answers1