Solved
Pivot table macro gives error subscript out of range
Posted on 2010-08-25
When I call this macro from access I get the error subscript out of range. When I run this in excel it runs fine It stops at this row Set WSD = Worksheets("tblEXPORTMETRICS")
Sub CreatePivot()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("tblEXPORTMETRICS")
' 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, 12)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets.Add.Cells(5, 1), TableName:="PivotTableStates")
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("state"), ColumnFields:="CHKVCHRCD"
' Set up the data fields
With PT.PivotFields("CHECKVIEWCHK.")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With
' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
' Added for the project files
WSD.Select