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.
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
' close book1 but if you want to keep open, delete those 2 lines
Application.DisplayAlerts = False