Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable - multiple consolidation ranges
Hi
I have, say, 10 sheets that I want to consolidate with a pivot table. But I also want to have the ability to see (spread) the data by its source, that is sheet? How can I do that if I have more than 4 sheets? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTable - multiple consolidation ranges
Hi Igor
Create a new Sheet in your file called All Data. Copy the Header row from one of your other sheets to this Sheet. Amend the Macro below, to set what is the last column of data on each sheet, and the column next to this which is where an entry will be created for the name of the source sheet against each row of data. Run the Macro below, which will combine data from as many sheets as there are in your file, which do not contain Report or Data in their name. You will have a consolidated set of data for all 10 sheets that you can then use to create a Pivot Table, the Source column showing where the data came from. Rename the Sheet where your PT is created to something including the word Report, so it will not be included in the Consolidation next time you run it. Sub CombineSheets() Dim Sht As Worksheet, SummarySht As Worksheet Dim NewRow As Long, LastRow As Long Const Lastcol = "Z" 'Set for last column of data Const SourceCol = "AA" ' next column to above Application.ScreenUpdating = False NewRow = 2 Set SummarySht = Sheets("All Data") SummarySht.range("2:65536").Delete For Each Sht In ThisWorkbook.Sheets 'Check it is not a Report or Data Sheet If InStr(Sht.Name, "Report") = 0 _ And InStr(Sht.Name, "Data") = 0 Then LastRow = Sht.range("A" & Rows.Count).End(xlUp).Row If NewRow + LastRow 65536 Then MsgBox "Cannot consolidate all data " _ & "as there are too many rows" GoTo Endsub End If Sht.range("A2:" & Lastcol & LastRow).Copy _ SummarySht.range("A" & NewRow) SummarySht.range(SourceCol & NewRow & ":" _ & SourceCol & LastRow + NewRow - 1) = Sht.Name NewRow = NewRow + LastRow - 1 End If Next Sht Endsub: With SummarySht Columns("A:" & SourceCol).EntireColumn.AutoFit range(SourceCol & "1") = "Source" Rows("1:1").RowHeight = 35 Rows("1:1").VerticalAlignment = xlTop range("A2").Select ActiveWindow.FreezePanes = True Application.ScreenUpdating = True End With End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier "IgorM" wrote in message ... Hi I have, say, 10 sheets that I want to consolidate with a pivot table. But I also want to have the ability to see (spread) the data by its source, that is sheet? How can I do that if I have more than 4 sheets? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working with multiple consolidation ranges. | Excel Worksheet Functions | |||
Multiple Consolidation Ranges | Excel Worksheet Functions | |||
PivotTable - Multiple Consolidation Ranges | Excel Worksheet Functions | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) |