Pivot Tables

[Mwithc3]

I am having trouble getting a pivot table to actually work. I have a
workbook divided into several worksheets. Each worksheet is a Bill of
Materials for a module. Each module, while being different is also made up
of some common parts. I want to summarize the bills of material into a
summary sheet that can give me totals of all parts required for an entire
project.

Each worksheet is set up exactly the same, and the columns are named fields
across the whole workbook. The fields are also formatted as text, currency,
etc.

When I start the pivot table wizard, it skips steps and creates a table that
I cannot do anything with. It also does not populate the field boxes with
the labels - they are just empty. I am wondering if this area of excel is
just corrupted in my installation.

Could really use some help. I checked out the link in another thread to
DataPig. Good info, but the wizard isn't even working right so I can't even
go that far.

 

[Roger Govier]

Hi

Trying to create PT's from data that resides on several sheets, requires you
to choose Multiple Consolidation ranges on the first screen of the dialogue.
The results from using Multiple Consolidation do not appear as you might
expect.

It would be better to aggregate all of the data from the individual BOM
sheets to one page of data, then Pivot that.
If you are still having difficulties, mail me a copy of the Workbook direct,
and I will see if I can see what is going wrong for you.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.

 

[Roger Govier]

Hi
File received and a copy sent back with macro included as below.
The first 4 index sheets were not anything to do with Bill of Materials, so
were excluded.
The macro reads through each other sheet, apart from anything with Report or
Data in the name, and consolidates the data to one sheet called "All Data".
A Dynamic Named range was set up to create a range called Data to pass to
the Pivot Table.

Name Data
Refers to ='All Data'!$A$1:INDEX('All Data'!$1:$65536,COUNTA('All
Data'!$B:$B),COUNTA('All Data'!$1:$1))

A sheet called All Data was created to provide the Summary
A sample PT was created on a sheet named Pivot Report to summarize the data.

Option Explicit

Sub combinesheets()

Dim Sht As Worksheet
Dim SummarySht As Worksheet
Dim NewRow As Long
Dim LastRow As Long

Application.ScreenUpdating = False
NewRow = 2
Set SummarySht = Sheets("All Data")
SummarySht.Range("2:65536").Delete
SummarySht.Range("A1:L1").AutoFilter

For Each Sht In ThisWorkbook.Sheets
If Sht.Index > 4 Then
If InStr(Sht.Name, "Report") = 0 And InStr(Sht.Name, "Data") = 0 Then


LastRow = Sht.Range("B" & Rows.Count).End(xlUp).Row
Sht.Range("A2:K" & LastRow).Copy SummarySht.Range("A" & NewRow)
SummarySht.Range("L" & NewRow & ":L" & NewRow + LastRow - 2) = Sht.Name
NewRow = NewRow + LastRow - 1
End If
End If
Next Sht

With SummarySht
Columns("A:N").EntireColumn.AutoFit

Range("A2").Select
ActiveWindow.FreezePanes = True
Range("A1:L1").Select
Selection.AutoFilter
Range("L1") = "Source"
Rows("1:1").RowHeight = 35
Rows("1:1").VerticalAlignment = xlTop
Application.ScreenUpdating = True

End With
End Sub

The macro also adds the source sheet name to each set of data consolidated,
and sets an Autofilter on the Summary sheet.

 

[Mwithc3]

Thanks so very much Roger. It does solve a rather large problem.

 

[Susan]

Hi,

I am having a similar problem. The difference is that I have two worksheets
because the data entirely filled the first one and then spilled over into a
second worksheet. So there is too much data to consolidate into one
worksheet. I have tried the multiple consolidation route and just cannot get
any proper headings or data in my pivot table. I select all of the relevant
rows/columns in each worksheet and end up with a pivot table listing only
rows or columns etc. Any ideas for me?

 

[Roger Govier]

Hi Susan

there is a method, where you can load much larger files than 65536 rows into
the Pivot cache.
I can't look out the answer for you right now, but will post gain in the
morning.