Copy data from another file to the main file using a macro in an excel report

This is one of the most useful macros, at least for me, it allows me to copy data from an excel file to my main file. Of course, when I am creating a macro for one of my reports, I put other codes in order to get the final result, what I want to say, it is not worth to use it alone, a manual copy is less annoying but not faster. I will explain 2 main actions, to use when a file is already open and to use when a file is already closed and saved in your PC.

formula excel formula excel

 

When I use the macro ?

When I have to copy data from an excel file to my main one, both files are opened so doing it straight away or only my main file is opened and I can do it later.

 

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

 

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

 

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
Dim i As Workbook
Dim j As Workbook
' book1 opens but if closed, put Workbooks.Open("\\fullpath\Book1.xlsx") instead of Workbooks("Book1.xlsx")
Set i = Workbooks("Book1.xlsx")
' xxx is your main file
Set j = Workbooks("xxx.xlsm")
' sheet1 from book1 and sheet2 to main file
' changesheet1/sheet2names by the correct name of your sheets
i.Worksheets("Sheet1").Range("A2:B10").Copy j.Worksheets("Sheet2").Range("A2")
' close book1 but if you want to keep open, delete those 2 lines
Application.DisplayAlerts = False
i.Close
End Sub

Interesting Management