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.
--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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.

--
Regards
Roger Govier

"Mwithc3" wrote in message
...
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.