New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

VBA to copy worksheet values to new workbook

Macros, VBA, Excel Automation, etc.

VBA to copy worksheet values to new workbook

Postby pbj1688 » Fri May 06, 2016 6:39 pm

Hello,
I'm trying to figure out how to copy multiple worksheets from multiple workbooks to an existing "master" workbook with existing worksheets. I only need to copy the values (to override any existing data in the master workbook worksheets).

I currently do this manually but would greatly appreciate a way to automate the process. I don't want to create a new worksheet, only overwrite the ones currently in the master.

Essentially, I have 4 spreadsheets (Sample1, Sample2, Sample3, Master).

In spreadsheets Sample1-3, there are tabs named:
Cost
Summary


In the Master spreadsheet, there are tabs named:

1-Cost - (data from sample1 cost tab)
1-Summary - (data from sample1 summary tab)
2-Cost - (data from sample2 cost tab)
2-Summary - (data from sample2 summary tab)
3-Cost - (data from sample3 cost tab)
3-Summary - (data from sample3 summary tab)

There is an additional tab:

All Samples Breakdown Compare
- (should contain breakdown from the 3 samples)

I'm looking for the master workbook to automatically be updated with:
Cost and Summary data from all 3 sample spreadsheets on their individual tabs
Breakdown of costs from all 3 samples on the breakdown tab
(The breakdown is contained on the Summary tabs of each sample spreadsheet b1:d5)

The breakdown data should start being pasted/updated on the Master All Samples Breakdown Compare tab in b2 for Sample1, e1 for Sample2, h1 for Sample3 (hope that makes sense!)

I've tried recording macro but it seems to take a long time to run and I've tried looking at code others have posted for similar situations but I don't understand enough (or any of it really) to make sense of why they fail.

And actually, maybe instead of limiting it to samples1-3 (as I might have more sample spreadsheets in the future) if there is a way to just have the code loop through all workbooks in a given folder, that would be awesome.

Would be eternally grateful for anyone able to help me with this, thank you!

Sample spreadsheets attached.

Thank you!
  • 0

You do not have the required permissions to view the files attached to this post.
pbj1688
Rookie
 
Posts: 1
Joined: May 6, 2016
Reputation: 0
Excel Version: 2010

Re: VBA to copy worksheet values to new workbook

Postby charles » Sat Jul 09, 2016 2:01 pm

Hi,

This is a sample code on this site that may help.
I believe you will need it in the "Master" workbook.
See if you modify it to your needs. If not come back and a member should help you.

Charles
viewtopic.php?f=5&t=2122
  • 0

There are other ways too do this but...

Be kind let us know if you posted somewhere else!



Charles
charles
Excel Badass
 
Posts: 632
Joined: Dec 10, 2011
Location: Mississippi
Reputation: 1
Excel Version: Excel 2003, 2007 and Mac 2011


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 11 guests