Excel Multiple Sheet Pivot

[R3D3]

I've searched everywere & tried everything before posting this so it is obviously impossible... unless you can solve it of course!

I can create Pivot tables without any problem.
However when it comes to Multiple Consolidation Ranges to do exactly the same using data from 4 different worksheets it all goes horribly wrong.

As an example, each of the 4 sheets are as follows:-
Sheet 1 (Location A):
Ref Name Center Period Amount
222 XXXXXX 123 05-01 23.00
333 YYYYYY 123 04-12 144.00
444 ZZZZZZ 456 05-01 2333.00
444 ZZZZZZ 666 05-02 111.50

The resultant Pivot table should be ....

Page = Locations (A,B,C,D)
Row = Center & Period
Fields = Ref, Name, Center, Period, Amount

Then I should be able to use the wizzard to drag the Fields into the Row/Column & manipulate the Data to Sum/Count as usual. This doesnt happen - I only get 'Page' and 'Row' and 'Column' and 'Value' fields and so at best I can only produce a 1-level pivot table which becomes useless.

I must be doing something wrong as pivot tables are so rich in functionality I cant believe it is so poor for multiple consolidations.
Thanks in advance.

 

[KenWright]

Must admit I absolutely hate the results you get when trying to use that for what you are doing. I will always combine the sheets into a single sheet and then start from there.

Assuming the sheets are all identical in structure, and there is nothing else in the workbook then I use the following to combine them all. The code also creates a new field based on the sheet names as a means of keeping whatever logic there was initially in splitting the data up between sheets.

Sub SummaryCombineMultipleSheets()

Dim SumWks As Worksheet
Dim sd As Worksheet
Dim Sht As Long
Dim lrow1 As Long
Dim lrow2 As Long
Dim StRow As Long

HeadRow = InputBox("What row are the Sheet's data headers in?")
DataRow = HeadRow + 1

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary Sheet").Delete
Application.DisplayAlerts = False
On Error GoTo 0

Set SumWks = Worksheets.Add

With SumWks
   .Move Before:=Sheets(1)
   .Name = "Summary Sheet"
    Sheets(2).Rows(HeadRow).Copy .Range("1:1")
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1").Value = "INDEX"
End With

With Sheets(2)
    ColW = .UsedRange.Column - 1 + .UsedRange.Columns.Count
    
End With

For Sht = 2 To ActiveWorkbook.Sheets.Count
   Set sd = Sheets(Sht)
   lrow1 = SumWks.Cells(Rows.Count, "B").End(xlUp).Row
   lrow2 = sd.Cells(Rows.Count, "A").End(xlUp).Row
   
   With sd
      '.Activate
      .Range(.Cells(DataRow, 1), .Cells(lrow2, ColW)).Copy SumWks.Cells(lrow1 + 1, 2)
       SumWks.Cells(lrow1 + 1, 1).Resize(lrow2 - (DataRow - 1), 1).Value = .Name
   End With
Next Sht

SumWks.Activate

End Sub

Guess I should fix it really so that it can work on selected sheets as opposed to defaulting to all, but just another of those things I'll get round to eventually.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[R3D3]

I could be faced with the same solution if there is no way around it. Unfortunately my example was rather simplistic and each sheet is different with common column headings that would hopefully be matched for a consolidation pivot table.

Still hopeful...