Loop to copy the same rows from multiple files to a master file

**allen123** · 07-25-2012, 09:13 PM

Hi! I am really quite new to macros and am trying to get up to speed

I am trying to create a macro which copies an individual row from over a hundred separate excel files (all formatted identically) and pastes them into a separate master file with them all listed consecutively. I assume I need a for loop but cant work out how to get it to loop through separate files. I then have trouble coding to get the pasting to move down a row on the master sheet.

Any help would be much appreciated!!!

Thanks

Alex

**protonLeah** · 07-25-2012, 10:14 PM

Post a simple example of your workbook.
When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

**allen123** · 07-26-2012, 12:04 AM

Hi- thank you for replying.

Each workbook contains daily summary information for 16 different water monitoring sites (WM-01 to WM-16). I need to create a separate workbook which contains every day of information for just 1 site (e.g. WM-14).

For example, in the simplified attached workbook, I need to copy rows 16-18 (site WM-14) and paste this information into another workbook. This process needs to be repeated for all the files in the folder (about 100 and all formatted identically) in order to obtain all of the information from all days of monitoring for just site WM-14 in one 'master' workbook.

I therefore need a code to loop through each file, copying rows 16-18 of each and pasting them consecutively one after another in a master workbook.

All help, greatly, greatly appreciated!
Thank you!

**protonLeah** · 07-27-2012, 05:48 PM

try this one:

Option Explicit

Sub Copy2Tab()
    Dim SiteIDList  As Range, _
        SiteID      As Range, _
        LastID      As Range, _
        Source      As Worksheet, _
        Dest        As Worksheet, _
        NextRow     As Long
        
    Application.ScreenUpdating = False

    Set Source = Sheets("Sheet1")
    With Source
        Set SiteIDList = .Range("A3:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
    End With
    
    For Each SiteID In SiteIDList
        'check for "(" in id and ignore
        
        If InStr(SiteID.Value, "(") > 0 Then
            SiteID.Value = Mid(SiteID, 1, InStr(SiteID.Value, "(") - 1)
        End If
        Err.Clear
        On Error Resume Next
        Set Dest = Sheets(SiteID.Value)
        If Err.Number > 0 Then
            Err.Clear
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = SiteID.Value
            Set Dest = Sheets(SiteID.Value)
            Source.Range("A1:R2").Copy Dest.Range("A1")
        End If
        
        'find the next row in dest sheet
        NextRow = Dest.Cells(Rows.Count, "A").End(xlUp).Row + 1
        NextRow = IIf(NextRow < 3, 3, NextRow + 1)
        Source.Range(SiteID.Address).EntireRow.Copy Dest.Cells(NextRow, "A")
    Next SiteID
    Source.Select
    Application.ScreenUpdating = True
End Sub

**allen123** · 07-29-2012, 09:15 PM

Thank you so much for this. It works well- I could never have written that myself!

The only issue is that I would like to be able to get the code to loop through every Excel file in the folder (i.e. get the source file to change). However, when the code runs through these files, the information from each file needs to be copied to a common (master) sheet for each site (e.g. WM-01) (i.e. the sheet created during the first loop), rather than opening a new set of sheets for each file.

For example, row 4 from both the attached files ('SWM_example1', 'SWM_example2') needs to be copied to the same 'WM-02' sheet (which was created by your fantastic code, shown in your example, 'SWM_example(bvj)'), rather than creating a new 'WM-02' sheet everytime the code loops.

In my mind you are already a genius, but if you can help me with tweaking the code to do this, you will be STELLAR.

Many thanks