ExcelBanter - Excel 2000 - Pivot Table Show Pages

Debra,

I have coded most of the creation of the pivot tables and pivot charts. At a
high level, the macro will run the following code:
Sub Summary()
Application.Run "'Scrap template.xlt'!ImpClnData"
Application.Run "'Scrap template.xlt'!CreatePivotTable"
Application.Run "'Scrap template.xlt'!CreatePivotChart"
End Sub

Here is the code for the CreatePivotTable and CreatePivotChart macros:

Sub CreatePivotTable()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PTRefresh As PivotTable
Dim PI As PivotItem
Dim PF As PivotField
Dim PRange As Range
Dim FinalRow As Long

Set WSD = Worksheets("SummaryPT")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT

'Define input area and set up a pivot cache
FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 11)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Ran ge("M5"), _
TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row fields
PT.AddFields RowFields:="CC", ColumnFields:="Data", PageFields:="Part"

' Set up the data fields
With PT.PivotFields("Scrap")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "#,##0"
End With

With PT.PivotFields("Good")
.Orientation = xlDataField
.Function = xlSum
.Position = 2
.NumberFormat = "#,##0"
End With

With PT.PivotFields("Total")
.Orientation = xlDataField
.Function = xlSum
.Position = 3
.NumberFormat = "#,##0"
End With

With PT.PivotFields("Scrap%")
.Orientation = xlDataField
.Function = xlAverage
.Position = 4
.NumberFormat = "0.00%"
End With

With PT.PivotFields("Mtlcost")
.Orientation = xlDataField
.Function = xlSum
.Position = 5
.NumberFormat = "$#,##0"
End With

With PT.PivotFields("Vbcost")
.Orientation = xlDataField
.Function = xlSum
.Position = 6
.NumberFormat = "$#,##0"
End With

With PT.PivotFields("Fbcost")
.Orientation = xlDataField
.Function = xlSum
.Position = 7
.NumberFormat = "$#,##0"
End With

With PT.PivotFields("Labcost")
.Orientation = xlDataField
.Function = xlSum
.Position = 8
.NumberFormat = "$#,##0"
End With

With PT.PivotFields("Totcost")
.Orientation = xlDataField
.Function = xlSum
.Position = 9
.NumberFormat = "$#,##0"
End With

' Ensure that we get zeroes instead of blanks in the data area
PT.NullString = "0"

' Turn off Grand Total row.
PT.ColumnGrand = False

' Display only Scrap% data
With PT.PivotFields("Data")
.PivotItems("Sum of Scrap").Visible = False
.PivotItems("Sum of Good").Visible = False
.PivotItems("Sum of Total").Visible = False
.PivotItems("Average of Scrap%").Visible = True
.PivotItems("Sum of Mtlcost").Visible = False
.PivotItems("Sum of Labcost").Visible = False
.PivotItems("Sum of Vbcost").Visible = False
.PivotItems("Sum of Fbcost").Visible = False
.PivotItems("Sum of Totcost").Visible = False
End With

Set PTRefresh = Worksheets("SummaryPT").Range("m5").PivotTable
PTRefresh.RefreshTable

End Sub

*****

Sub CreatePivotChart()

Range("M3").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("SummaryPT").Range("M3")
ActiveChart.Location Whe=xlLocationAsNewSheet

ActiveWindow.Zoom = 75
With ActiveChart.PageSetup
.LeftHeader = "SKF Sealing Solutions"
.CenterHeader = "Scrap Report"
.RightHeader = "&D"
.LeftFooter = "Elgin Plant"
.CenterFooter = "&A"
.RightFooter = "Page &P of &N"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.ChartSize = xlFullPage
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With

'ActiveChart.PivotLayout.PivotTable.RefreshTable
'ActiveChart.ChartTitle.Delete

Sheets("Chart1").Select
Sheets("Chart1").Name = "SummaryPC"
Sheets("SummaryPC").Select
Sheets("SummaryPC").Move Befo=Sheets(5)

End Sub

Thanks in advance Debra (and thanks for your awesome website and assistance
for us Excel MVP wannabe's :)

"Debra Dalgleish" wrote:

If you use the Show Pages command manually, do you get data on the
worksheets?
What code did the macro recorder produce?

To create the pivot charts, you can create some of the code by using the
macro recorder. Are you having problems with a specific part of the code?

GDCross wrote:
Question #1: I am trying to write VBA code to show pages from a pivot table.
The code from the macro recorder produces the worksheets, however, there is
no data in them and I don't know why?

Question #2: I need to write VBA code to generate PivotCharts off of these
Pivot Tables. Can anyone suggest any references for me?

Thanks in advance. GDCross

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html