How do I combine worksheets in XL 2010?

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
contrainAsked:
Who is Participating?
 
ProfessorJimJamCommented:
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
 
becraigCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ProfessorJimJamCommented:
Hi,  do you want this to be done by VBA macro?
0
 
Glenn RayExcel VBA DeveloperCommented:
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
 
contrainAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
ProfessorJimJamCommented:
@contrain  

please respond if the solution i provided helped you, please close this thread.
0
 
Monty Millersystems coordinatorCommented:
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
 
contrainAuthor Commented:
This macro gave me the results I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.