Pivot table macro gives error subscript out of range

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
    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
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

andreyman3d2kConnect With a Mentor Commented:
Also, if I understand correctly, the macro in in personal.xls, but you wish to run it on the other file.

Try swapping the order of these two lines of code:

Set exbook = appXL.Workbooks.Open("C:\" & fname)
Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")

so make it:

Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")
Set exbook = appXL.Workbooks.Open("C:\" & fname)
Serena2345Author Commented:
this is how I call the macro
Public Sub RunXlMacro()

Dim fname As String
fname = "Ipaymetrics by state_" & Format(Date, "yyyymmdd") & ".xls"
Dim exbook As Excel.Workbook
Dim ws As Worksheets
Dim appXL As Excel.Application
Set appXL = New Excel.Application
Set exbook = appXL.Workbooks.Open("C:\" & fname)
Set exbook = appXL.Workbooks.Open("S:\Payroll\XLTEMPLATE\Personal.xls")
appXL.Visible = True
appXL.Interactive = True
appXL.Run "Personal.xls!CreatePivot" ' run the excel macro
MsgBox "Done!", vbOKOnly
I think the syntax is something like

Set WSB = exbook.sheets("tblEXPORTMETRICS")

Assuming exbook is the name of the workbook...
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Serena2345Author Commented:
Sorry does not help
Serena2345Author Commented:
Most humble thanks you guys are the best
No problem. Amazed that my utterly mediocre VBA skills (or lack thereof) actually resulted in something useful : )  Thanks for the grade & points!

All Courses

From novice to tech pro — start learning today.