Copy info from 2 source files to Master file

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Change the file names and destination sheet name to suit...

Rich (BB code):
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
 
Solution

tmsousa

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi AlphaFrog,

This is brilliant, works perfect, thanks a lot!

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

VBA Code:
wsDestination.UsedRange.Columns("B").Offset(1).ClearContents

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

VBA Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
You're welcome. Thanks for the feedback.

VBA Code:
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

New Member
Joined
May 14, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Ahh that makes sense! I was not understanding at all why it was using column C :)

Thanks a lot for the help once again!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top