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?

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?