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.

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
' change sheet1/sheet2 names 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