Copy Data from Multiple Workbooks into another Workbook

**trust_lord** · 10-26-2011, 09:53 PM

Dear all,

I have eight (8) workbooks. I want to copy data of sheet1, except row 1, of each seven (7) workbooks or source workbooks into cell A2 of specific sheet of the 8th workbook or destination workbook.

Each source workbook has thirteen (13) columns , i.e, column A to Column F.
My source workbooks are ff.:
1. Journal Voucher Schedule.xls
2. Cash Disbursement Schedule.xls
3. Loan Disbursement Schedule.xls
4. Cash Receipt Schedule.xls
5. Cash Settlement Schedule.xls
6. Loan Amorization Schedule.xls
7. System Journal Voucher Schedule.xls

My destination workbook is called WTB.xlsm with the ff. sheet name where data are to be copied in cell A2 of each sheet:
1. JVS (sheet no. 10) -copy data of Journal Voucher Schedule.xls here
2. CDS (sheet no. 11)-copy data of Cash Disbursement Schedule.xls here
3. LDS (sheet no. 12)-copy data of Loan Dibursement Schedule.xls here
4. PCTB (sheet no. 13)-copy data of Cash Receipt Schedule.xls here
5. CSS (sheet no. 14)-copy data of Cash Settlement Schedule.xls here
6. LAS (sheet no. 15) -copy data of Loan Amortization Schedule.xls here
7. SJVS(sheet no. 16)- copy data of System Journal Voucher Schedule.xls here

Once the data are copied, I want to close the source workbooks without save while the destination workbook will be saved but remain open or active.

I want to locate my macro in my personal.xlsm files and not in the destination workbook nor in the source workbooks.

Please help. Thank you.

**JBeaucaire** · 10-27-2011, 01:12 AM

Here's one method, should get you started:

Please Login or Register  to view this content.

**trust_lord** · 10-27-2011, 04:14 AM

Dear JBeaucaire,

Your code works like magic! Thanks a lot.

What if i do something first to source workbooks, say i will concatenate columns D&E, and Columns H& I before i copy the same to destination workbook.

Is it possible?

trust_lord

**JBeaucaire** · 10-27-2011, 10:43 AM

The answer to almost every question like this is probably yes. So, what if?

**trust_lord** · 10-27-2011, 09:11 PM

Could you please help me by inserting the code for that in the previous program that you made.

Thanks again.

**JBeaucaire** · 10-28-2011, 12:41 AM

I see no need for a change to the code based on anything you might have done to the data itself. The macro is copying the usedrange of data as a whole to the appropriate sheet. You might want to change this one line of code to clear all formatting and previously merged cells, too:

Please Login or Register  to view this content.

**trust_lord** · 10-28-2011, 04:05 AM

Dear JBeaucaire,

I tried to amend the macro but can't make it run.

All I wanted is to include the codes below within the macro to edit each source workbook before each is copied into the source workbook.

'Concatenates columns D & E on Cell F2
Columns("f").Insert Shift:=xlToRight
range("F2").FormulaR1C1 = "=RC[-1]&RC[-2]"
range("F2").Autofill Destination:=range("F2:F" & range("E" & Rows.Count).End(xlUp).Row)

'Get the difference of debit column and credit column
Columns("j").Insert Shift:=xlToRight
range("J2").FormulaR1C1 = "=+RC[-2]-RC[-1]"
range("J2").Autofill Destination:=range("J2:J" & range("I" & Rows.Count).End(xlUp).Row)

'To get the sum of each column-h, i, & j
Dim rng As range
Dim h As range
Set rng = range("h1:h" & range("h1").End(xlDown).Row)
Set C = range("h1").End(xlDown).Offset(1, 0)
C.Formula = "=SUM(" & rng.Address(False, False) & ")"
C.Copy C.Resize(1, 3)

Thank you.

**JBeaucaire** · 10-28-2011, 05:08 AM

As per forum rules, please EDIT your post above and add code tags around the code like I've done in post #2 above.

**JBeaucaire** · 10-28-2011, 05:14 AM

Try this:

Please Login or Register  to view this content.

**trust_lord** · 10-28-2011, 05:39 AM

Dear JBeaucaire,

Sorry for not following the forum rules on my previous post.

I tried to amend the macro but can't make it run.

All I wanted is to include the codes below within the macro to edit each source workbook before each is copied into the source workbook.

Thank you.

Please Login or Register  to view this content.

**JBeaucaire** · 10-28-2011, 07:05 PM

What did you try to "make it run". Why are you posting the same code again? You should be inserting the code I provided above into the macro at point in the macro where you would like it to execute.

**JBeaucaire** · 10-28-2011, 07:14 PM

This is supposed to run on every sheet1 before the data is imported? Yeah, that's a little more involved.

Please Login or Register  to view this content.

**trust_lord** · 10-29-2011, 10:33 PM

Dear JBeaucaire,

It now works perfectly fine. Just made a small change in the code, i.e., put "=' before the word "SUM" to get the column total.

Thank you again for your great help.

trust_lord

Copy Data from Multiple Workbooks into another Workbook

LinkBack

Thread Tools

Rate This Thread

Display

Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Re: Copy Data from Multiple Workbooks into another Workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions