Sunday, February 7, 2016

Copy from one workbook and paste into another

One of my friend was trying to copy the data from one work book to other workbook. We can automate this using the VB macro. Here is the solution for it:

I have a work book "test1.xls" which has some contents as shown:



You can download it from here: test1.xlsx

And I have another workbook test2.xlsx which has the contents below:



You can download it from here: test2.xlsx

Now I have to create a VB Micro which will take the contents of these two xlx and adds to Reports.xlsx. The contents of Reports.xlx should look as below:



I want to copy the contents test1.xlx and test2.xls without opening these files and copy them and paste to report.xlsx. Now open the reports.xls and copy the micro I have added:

Create a micro as test() and paste the below code:
Sub test()

Application.ScreenUpdating = False
Workbooks.Open Filename:="F:\Projects\freelancer\vba\test1.xlsx"
'For I = 1 To 50
 Workbooks("test1.xlsx").Sheets("Sheet1").Range("B2:B15").Copy
 Workbooks("Report.xlsm").Sheets("Sheet1").Range("B2").PasteSpecial
    'Workbooks("Report.xlsm").ActiveSheet.Range("B2:B15").Value = Workbooks("test1.xlsx").Sheets("Sheet1").Range("B2:B15")
'Next I
Windows("test1.xlsx").Close
 
 Workbooks.Open Filename:="F:\Projects\freelancer\vba\test2.xlsx"
 Workbooks("test2.xlsx").Sheets("Sheet1").Range("B2:B15").Copy
 Workbooks("Report.xlsm").Sheets("Sheet1").Range("C2").PasteSpecial
Windows("test2.xlsx").Close

Application.ScreenUpdating = True
 Workbooks("Report.xlsm").Save
End Sub
Run this micro and you get the expected result. You can download this report.xlsm which containg the micro from here report.xlsm
That's it. Happy coding.

No comments: