Link to home
Start Free TrialLog in
Avatar of Nirvana
NirvanaFlag for India

asked on

consolidate multiple workbooks into one

I had this question after viewing Excel VBA to consolidate multiple workbooks into one.

1. I have master workbook and multiple other workbooks all workbooks need to get consolidated to the master workbook based on the column heading (in sheet2 of master workbook)

for example if I have column as "index" in ColA of master workbook and "index" in column "E" of "country" workbook after consolidation it have to get copied from E to A(append)

2. sheet name of other workbook needs to get copied to column q of master workbook

3. master workbook has formulas in col A, D and E these have to get copied to all the active rows

4. Refresh all pivots in "pivots" sheet

Thanks a lot for brilliant code.please help me out
Avatar of Michael
Michael
Flag of Belgium image

It would help a lot if you could post a sample workbook with some (dummy) data of the input data and the output data (master workbook).
Avatar of Nirvana

ASKER

Sorry for the delay in responding. attached are the files (master and source files)
SalesReport1-source1.xlsx
SalesReport1-source2.xlsx
SalesReport1-source3.xlsx
SalesReport1-Master.xlsx
Thank you for posting the files.

Are the input files all located in the same folder – i.e. import all excel files in a particular folder? Or is there a fixed set of files which need to be imported?

And before consolidating the sales reports in the master workbook, do you just want to clear the "index" sheet in the master workbook?
Avatar of Nirvana

ASKER

yes all the input files will be in the same folder

yes the index sheet in master should get cleared however the formula columns needs to be there.

Thanks a lot again for your time
Thank you for your reply. One more question.

The data in the file 'Source1' starts in row 6; in the other source files, it starts at row 1. Does this vary per file?
Avatar of Nirvana

ASKER

the data in source files will not vary
Please try the macro in the attached file.

Modify this line to your file path:
MyPath = "C:\Users\Nirvana\Sales reports"

The macro then opens all excel files in that folder and copies the data (from the second worksheet) over to the master workbook including the name of the input worksheet.

I've assumed that the data in the input files starts at row 1.

If you run into any issues, just let us know.

(The vba code is adopted from Ron de Bruin's example)
Avatar of Nirvana

ASKER

Hi Michael the attachment is missing
Forgot to attach it, sorry
SalesReport1-Master.xlsm
Avatar of Nirvana

ASKER

HI Michael I have done initial testing and it works wonderfully well. thanks a lot just give me a day to close the ticket. Thanks a lot.
Avatar of Nirvana

ASKER

Hi Michael, hope you had a good weekend.

the files that i have provided are sample files and not the actual ones. when i ma trying to incorporate the same in original, I have below challenges, please guide me, I wanted to review code and also learn little bit of code so that i can write code like you one day..

i have some vlookup formulas so i have changed the data type as string and put actual formula in

BaseWks.Cells(rnum, "A"). _
Resize(.Rows.Count).FormulaR1C1 = "=VLOOKUP(B3,Index!A:B,2,0)"

there are some extra columns in source file; how can i map columns with heading names meaning if the column heading of source file with the heading of master file than append

thank you
ASKER CERTIFIED SOLUTION
Avatar of Michael
Michael
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nirvana

ASKER

Hi Michael,

say master file has Column A as "customer" but in source files Column D as "Customer" so it have to find Customer and then append in master file. extremely sorry if I was not articulated it properly and below is the structure of my columns in master sheet. I can modify formula columns with the solution you have provided however mapping based on the column headers is what i am struggling with

Column A,B,E,F,Q,R,S,T has Formulas(vlookup and some othe funtions)
Column C,D,G,H,I,J,K,L,M,N,O (data needs to be picked from source files)
Column P should pick the sheet name of source file and update in column p of master file.

Thanks again for all your help
I'm sorry, but unfortunately I'm still not sure what you mean.
Could you provide an example file? Or maybe send it privately?
Avatar of Nirvana

ASKER

Hey Michael, How do I send the file privately? can you provide email id? thank you
I've send you a private message through this website.