Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I combine worksheets in XL 2010?

Posted on 2014-08-27
10
Medium Priority
?
236 Views
Last Modified: 2014-09-09
In XL 2010, I work on worksheets of varying sizes, sometimes dozens of columns and hundreds of rows on each sheet. I would like to know a way to combine all of the sheets, regardless of their size, into one sheet.
Combine-Worksheets.xlsx
0
Comment
Question by:contrain
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40289248
Here is a how to on getting this done:
http://office.microsoft.com/en-us/excel-help/consolidate-data-from-multiple-worksheets-in-a-single-worksheet-HP010342300.aspx


The basic idea is to use data consolidation, but there are other considerations.
The link above should simplify this for you
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40289290
With just some minor tweaking to the Europe sheet to align it with the other two (United States, Asia), you can use 3D cell referencing to sum up the regional sheet values and display the total on the master.

For example, on sheet "Ad Budget", you could insert the following formula in cell C5 and then copy it down and over to pick up the totals automatically:
=SUM('United States:Europe'!C7)

Again, this only works if the Europe sheet has column A or B removed and a row (2,3,4) removed.  This is key.

I've attached a modified version of your file to demonstrate.

Regards,
-Glenn
EE-Combine-Worksheets.xlsx
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40298049
Hi,  do you want this to be done by VBA macro?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40301405
Hi,

Did you have a chance to review and or test my solution?  If so, and it will work for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

Otherwise, let us know if you have any other issues.

Thanks,
 -Glenn
0
 

Author Comment

by:contrain
ID: 40302345
I'm not looking to consolidate data, I don't want to add, for instance, cell C6 on one sheet to cell C6 on the other sheets, I would like all 4 sheets on one sheet, each sheet taking up its own space on one contiguous sheet. I wouldn't mind it begin done via VBA macro or any other way, as long as I can get all of the data onto one sheet.

Thanks,
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40302360
Do you mean something that would appear like this?
combine sheets
This is a literal combination of the used ranges from each of the sheets onto one sheet.  The same methodology was used (select the active range and paste one row down from existing data in the destination sheet).  As you can see, any variances in the source data (ex., Europe) will not be accounted for, so formatting issues may be significant.

It can be done with VBA.

-Glenn
0
 
LVL 27

Accepted Solution

by:
ProfessorJimJam earned 2000 total points
ID: 40303864
Hi,

run the macro called CopyRangeFromMultiWorksheets  in the uploaded file and you will get what you need.  
it will also create a column reference to show which data is from which sheet which you can delete if you dont need.

cheers,
G--Combine-Worksheets.xlsb
0
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 40309605
@contrain  

please respond if the solution i provided helped you, please close this thread.
0
 

Expert Comment

by:Monty Miller
ID: 40310494
Are the sheets similar in content? IE.. all have the same headers starting on the same row with the same number of columns just different number of rows?
0
 

Author Closing Comment

by:contrain
ID: 40312436
This macro gave me the results I was looking for.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question