Excel 2000 - Pivot Table Show Pages

[Guest]

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]

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?

 

[Guest]

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:=xlDatabase, _
SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("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 Where:=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 Before:=Sheets(5)


End Sub

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

 

[Guest]

Debra,

In my haste, I did not check which post this was regarding. I have answered
these questions but if you can refer to my post "Excel 2k Pivot Table refresh
scenario" this is my last hurdle in this macro. Looking forward to your reply.

GDCross