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.
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
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.
Labels: Career, Pro Excel Tips