Beginner Macro Question - unable to get the pivotfields property of the pivottable class

[ChristopherOdd]

Hi everyone, I am very new to using macros with excel.

I'll give you a quick summary of what I'm trying to do:

1. Output a query from Access to Excel.
2. Create pivot table from data
3. Format pivot table

I've recorded the macro and played it back correctly (without error) on the original file. When I want to use a different set of information I get an error code that reads "unable to get the pivotfields property of the pivottable class."

When I choose the debug option, it highlights this row:

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of CountOfCODE")

Since I will be dealing with different amounts of data all the time, how can I manipulate this macro to run correctly each time?


I will paste the entirety of the code here:

Sub WrapCode()
'
' WrapCode Macro
' Macro recorded 1/29/2014 by Deutscher, ChrisBLAN
'
' Keyboard Shortcut: Ctrl+h
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'For TL''s Wrap Code Ph Numbers -'!R1C1:R41335C9").CreatePivotTable _
TableDestination:="", TableName:="PivotTable3", DefaultVersion:= _
xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").PreserveFormatting = False
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("Name", _
"Data"), ColumnFields:="CODE_DESCRIP", PageFields:="Team leader"
With ActiveSheet.PivotTables("PivotTable3").PivotFields("CountOfCODE")
.Orientation = xlDataField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("CountOfCODE").Orientation _
= xlDataField
Range("C5").Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of CountOfCODE")
.Calculation = xlPercentOfRow
.NumberFormat = "0.00%"
End With
ActiveWindow.DisplayZeros = False
Columns("B:B").Select
Selection.Font.ColorIndex = 2
Range("B1").Select
Selection.Font.ColorIndex = 1
Columns("A:A").EntireColumn.AutoFit
Range("C4:AF4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 75
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:AF").Select
Selection.ColumnWidth = 11.86
ActiveWindow.LargeScroll ToRight:=-2
Range("C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T" _
).Select
Range("T1").Activate
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range( _
"C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD" _
).Select
Range("AD1").Activate
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Range( _
"C:C,D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T,U:U,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AF:AF" _
).Select
Range("AF1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
ActiveWindow.LargeScroll ToRight:=-1
Cells.Select
Range("D16").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("C5").Select
End Sub




​

 

[Trevor G]

Welcome to the forum, before you go down this route have you looked to create a Pivot Table in the database? You can create one in the forms section.

 

[ChristopherOdd]

Welcome to the forum, before you go down this route have you looked to create a Pivot Table in the database? You can create one in the forms section.

Thanks Trevor G, I haven't played around with forms/pivot tables in access. I'm a bit set in my current ways of doing the above activity as it is, and stepping into macros is scaring me

 

[ChristopherOdd]

Welcome to the forum, before you go down this route have you looked to create a Pivot Table in the database? You can create one in the forms section.

I was messing around with the Access Pivot Table but I can't seem to get my rows to show as "percentages of rows" like I can in excel. Seems like if someone could help with the macro, it would be a bit easier.

 

[ChristopherOdd]

I hate to bump this, but does anybody have an idea?

 

[Trevor G]

Chris drop a copy of the workbook into Drop Box or another system and place a link into your thread. I will take a look over the weekend.