Link to home
Start Free TrialLog in
Avatar of Jagwarman
Jagwarman

asked on

Copy data from closed workbooks part 2

I need to copy data from each workbook which will be in the same folder. Each workbook will have a different name. Each workbook will have a sheet named Volumes and a sheet named C&A

I need to copy both of these sheets from each of the workbooks into a my active workbook and name the sheets based on the name in cell C3. [i.e. If C3 = London, name Sheets London Volumes and  London C&A]

In the Active workbook will be a sheet named 'Master'. I need to collate the numbers from each sheet into the respective cell in the Master Sheet. [i.e. starting at cell D8] (add the total of all cells D8 and enter total in cell D8 in 'Master' Sheet)

I have attached a sample file but have only shown 2 Extra sheets and only shown data in D and E, group A and B.
Avatar of Jagwarman
Jagwarman

ASKER

forgot to attach file :-(
Copy.xlsm
Avatar of Subodh Tiwari (Neeraj)
No sample workbook attached.

I need to copy both of these sheets from each of the workbooks into a my active workbook and name the sheets based on the name in cell C3. [i.e. If C3 = London, name Sheets London Volumes and  London C&A]

C3 of target Sheets and target workbook?
Are all the products listed in column C of Master Sheet also compulsorily listed in all the target sheets and in the same sequence?
sample workbook should be there now
You have not answered my queries I asked in my last reply yet.

Library Reference to be added:  Microsoft Scripting Runtime

Here is the part one of the task.
Please find the attached workbook and place this workbook in the folder where all your target files as saved and run the code by clicking the Copy Sheet button on the Master Sheet.

Please confirm all your target sheets are being copied to the Master workbook without an issue.
We will take the second part then.
Copy.xlsm
Sorry:

Are all the products listed in column C of Master Sheet also compulsorily listed in all the target sheets and in the same sequence?

Yes they are.

Please confirm all your target sheets are being copied to the Master workbook without an issue.

Yes they are
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India 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
That is brilliant sktneer. I've only tested it on 3 files but will test on remainder this afternoon.
If it has worked for 3 files successfully, it will also work for rest of them.
Make sure your Master workbook doesn't contain any Volumes of C&A sheets before running the code.
sktneer never fails to amaze. Brilliant
Glad it worked for you. :)
There could well be more like this :-)