Combining Spreadsheets in VBA
Hi All,
I have two features for three products (A,B and C). These features are: Dist and Exp.
There are 6 spreadsheets that I want to combine by feature. So, what I would like the Macro in spreadsheet
1. 'Consolidate_exp': Copy and paste the contents from the three spreadsheets (A_exp, B_exp and C_exp) one after another so that I have all the data in a master spreadsheet 'Consolidate_exp'.
2. 'Consolidate_dist': This should create a new Sheet (Sheet1, say) and copy and paste all contents from the three spreadsheets (A_dist,B_dist and C_dist) in the Sheet1.
I have attached the sample files. Any help will be highly appreciated.
Kind Regards
Siddharth
| Attachment | Size |
|---|---|
| Combine.zip | 45.06 KB |
Macro to solve your problem!
Sub consolidate()
Workbooks("a_dist").Sheets("Sheet1").Range("a:a").Copy Range("A:A")
Workbooks("b_dist").Sheets("Sheet1").Range("a:a").Copy Range("B:B")
Workbooks("c_dist").Sheets("Sheet1").Range("a:a").Copy Range("C:C")
End Sub
You just paste the sub above in a module in workbook "consolidate_dist" and run it.
For the other one, you just paste the following sub in a module in workbook "consolidate_exp"
Sub consolidate()
Workbooks("a_exp").Sheets("Sheet1").Range("a:a").Copy Range("A:A")
Workbooks("b_exp").Sheets("Sheet1").Range("a:a").Copy Range("B:B")
Workbooks("c_exp").Sheets("Sheet1").Range("a:a").Copy Range("C:C")
End Sub