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.
JagwarmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JagwarmanAuthor Commented:
forgot to attach file :-(
Copy.xlsm
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Are all the products listed in column C of Master Sheet also compulsorily listed in all the target sheets and in the same sequence?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JagwarmanAuthor Commented:
sample workbook should be there now
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
JagwarmanAuthor Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay.
Please find the workbook with the complete code and click the button Copy Sheet to run the code and see if you get the desired output.
Copy.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
That is brilliant sktneer. I've only tested it on 3 files but will test on remainder this afternoon.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
JagwarmanAuthor Commented:
sktneer never fails to amaze. Brilliant
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad it worked for you. :)
JagwarmanAuthor Commented:
There could well be more like this :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.