Consolidate "Specific Name" worksheet data from multiple workbooks and into Master Workbk

[bala04msw]

Dear All,

Kindly help for the following requirements.

I have multiple (8) excel workbooks in a folder. such as India, China, Australia, USA, UK, Germany, France, Italy and each workbook has 3 worksheets. (A, B, C)


Requirement as follows:

1. I have a consolidation sheet called "Master", which has 3 worksheets (A, B, C) as same as excel workbooks, which have been kept in the folder.

2. Now, i want to copy "A" worksheets data from "India, China, Australia, USA, UK, Germany, France, Italy" and consolidate in "Master" consolidation workboook, "A" Worksheet. The data range starts from "A4".


3. Like wise, i want to copy "B" and "C" data from "India, China, Australia, USA, UK, Germany, France, Italy" and and consolidate in "Master" consolidation workboook, "B" and "C" respective Worksheet. The data range starts from "A4".


Kindly do the needful.

Regards

[dmcgov]

when the data is copied between india and master, does the range start from A4 or A1? (on any sheet A,B,C). in other words where does the data start on the master sheet A (or B,C) range?

maybe upload your master workbook, so i can take a peek at the structure?

[bala04msw]

when the data is copied between india and master, does the range start from A4 or A1? (on any sheet A,B,C). in other words where does the data start on the master sheet A (or B,C) range?

maybe upload your master workbook, so i can take a peek at the structure?

Dear Mr. dmcgov

The range starts from A4 in both copied area and paste area also. Meaning... The data starts from A4 in India (A, B, C in three worksheets) and paste the data in Master Workbook is also A4 for A, B and C


Regards

Regards

[dmcgov]

sorry it took so long to do this, but give it a shot. i put it in a button on the master sheet:

Please Login or Register  to view this content.

let me know if this works for you.

[bala04msw]

Dear Mr. dmc,

Thanks for your time and efforts for my request. Basically I have 3 things to discuss and acquire your guidance.


1. Very first line of the code

Dim x, y, z

- what x, y, z represent?

2.

x = Array("India", "China", "Australia", "USA", "UK", "Germany", "France", "Italy")

- The code acts for getting access for only these mentioned 8 workbooks. Without mentioning the files name, Is there any other way to get access, like " If I click "Command Button", it should lead to open a window and I will choose files as many as I want?


3. When I use the code, which you have shared, I am facing "Runtime error - 9 - subscript out of range". Debug exactly on

Set wbCountry = Workbooks(x(y) & ".xlsx")

Kindly help me on this request.

Regards

[dmcgov]

on the first question, that is sloppiness on my part, please delete the line
on the second question, that's a lot of extra code to multiselect. wish you had said that to start with.
on the third, put this line before the set wbCountry and tell me what the result is

Please Login or Register  to view this content.