Copy info from 2 source files to Master file

[tmsousa]

Hi!

I'm looking to import data from 2 files into 1 single master file. Both source files will always be in the same directory with the same name files. A simple description would be:
- Delete content on master workbook in B1:B_Last row
- Copy info from source workbook 1, cells A6:Q_Last row
- Paste into B2 on master workbook
- Copy info from source workbook 2, cells A6:Q_Last row
- Paste into (current) B_last row+1 on master workbook

I'm not being able to do this automatically, I can only make it work when I ask the user to open the source files manually... Is there any way to do this without user inputs (besides clicking to run the macro).

Many thanks!
Tiago

 

[AlphaFrog]

Change the file names and destination sheet name to suit...

Sub Updater()
    Dim strFile1 As String, strFile2 As String
    Dim wsDestination As Worksheet
   
    strFile1 = ThisWorkbook.Path & "\MyFile1.xlsx"
    strFile2 = ThisWorkbook.Path & "\MyFile2.xlsx"
   
    Set wsDestination = ThisWorkbook.Sheets("Sheet1")
   
    Application.ScreenUpdating = False
   
    wsDestination.UsedRange.Columns("B").Offset(1).ClearContents
   
    With Workbooks.Open(strFile1).Sheets(1)
        .Range("A6:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
        wsDestination.Range("B2").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Parent.Close False
    End With
   
    With Workbooks.Open(strFile1).Sheets(1)
        .Range("A6:Q" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
        wsDestination.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .Parent.Close False
    End With
   
    Application.ScreenUpdating = True
End Sub

 

[tmsousa]

Hi AlphaFrog,

This is brilliant, works perfect, thanks a lot!

Just 2 notes not as criticism but rather (hopefully) constructive feedback:

wsDestination.UsedRange.Columns("B").Offset(1).ClearContents

This is deleting content from cells C1 to C_Last row (weird?). I changed it to

wsDestination.Range("B2:R10000").ClearContents

as it is not really a problem if it's not dynamic.

The other change was on the second "with" function, it was referencing file 1 rather than 2, but again, just FYI

Really appreciate the help, like I said, this works super well!! Very quick and efficient

Cheers,
Tiago

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

wsDestination.UsedRange.Columns("B").Offset(1).ClearContents

If column A is empty, the used range starts at column B.
.UsedRange.Columns("B") refers to the 2nd column of the used range, or in your case; column C
Your code edit is fine.

I just copied the File1 code block for File2 and missed that edit. Thanks.

 

[tmsousa]

Ahh that makes sense! I was not understanding at all why it was using column C

Thanks a lot for the help once again!