Excel Tip #15 - Macro to Pull Information from One Excel Workbook into Another

In accounting sometimes we download GL detail from the accounting system or pull information from a third-party source and then we dump that data into an excel workbook. Often times that information is used in multiple excel workbooks. But it becomes cumbersome when you have to manually copy and paste that information from one spreadsheet to another. Why not just have a macro do it for you? Yes, we've created a macro that will copy data from one spreadsheet (doesn't have to be open) into another spreadsheet instantly.

Here is the solution. Though you will need to make some changes to the code below.

a] Get_Copy_Data - This is what you want to name your macro. Leaving it as is will not affect the code.

b] CopyDataSheet - This is the name of the sheet you would like the data copied to.

c] C:\Users\z3n\Desktop\hapy.xlsx - This is the file path of the excel workbook that already has the data. Make sure it is in the first tab.

d] hapy.xlsx - This is the excel workbook that has the data already

1) Hit Alt + F11 (this will open the VBE Window)
2) Select 'Module' from the 'Insert Menu' and paste the code below.

 Sub Get_Copy_Data()
    Sheets("CopyDataSheet").Select
    Range("D7").Activate
    Selection.ClearContents
ThisWorkbook.Activate 'start in THIS workbook
 Sheets(1).Select 'switch to data import sheet
'Opens source file (this filename never changes)
 Workbooks.Open Filename:="C:\Users\z3n\Desktop\hapy.xlsx", ReadOnly:=True
Workbooks("hapy.xlsx").Activate 'switch to source workbook
 Sheets(1).Select 'switch to source data sheet
 [a1].CurrentRegion.Copy 'Copy data to clipboard
ThisWorkbook.Activate 'Return to THIS workbook
[G7].PasteSpecial Paste:=xlPasteValues 'paste data to import start cell
[a1].Select 'cancels highlighted paste region
Workbooks("hapy.xlsx").Close 'close source data workbook
Sheets("CopyDataSheet").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

At the moment, you'll have to save this in your "Personal" macro book. It has trouble when you save the macro in the workbook itself.