Combining huge data from multiple sheets

mycoplasma

New Member
Joined
Apr 16, 2013
Messages
4
Hi All,

Is there a way to combine a year long data which is available under one workbook under 12 multiple worksheets in the same workbook, so that i can do a pivot across all the data to bring out reports of my liking.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
try this

Assuming you will create a blank tab called summary to put all the data.

Change your range to copy in the wks.range line to what you are copying,

and it is set up to start pasting the values in the first blank row in column A of "summary"


Sub copyall()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If Not wks.Name = "Summary" Then

wks.Range("a1:Z" & wks.Cells(Rows.Count, "a").End(xlUp).Row).Copy _
Destination:=Worksheets("Summary").Cells(Rows.Count,"A").End(xlUp).Offset(1)
End If
Next

End Sub
 
Upvote 0
Welcome to the Board!

If you use the keyboard shortcut ALT+D+P you'll bring up the Pivot Table wizard. One of the options is for Multiple Consolidation Ranges. Provided your sheets are all the same, you should be able to use that to consolidate the ranges into one PT.

HTH,
 
Upvote 0
in your speadsheet press alt-F11 to bring up the VBA editor.

double click on the sheet name where you want the code to run.

paste the code in there.

put your cursor somewhere inside the code and hit F5.

Make you test this on a test file first as VBA code doesnt have an undo function.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top