Run-Time Error 1004 When Running Pivot Table Code (1 Viewer)

Option Explicit

Sub main_prog()

    Call td_metrics_import
    Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
    Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
    Call create_graph

End Sub



Sub pt_td_metrics(Chrt_Pg_Name As String, p_tbl_name1 As Variant, p_tbl_name2 As Variant)

Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim ptCache As PivotCache
Dim prange As Range
Dim lastRow As Long
Dim lastCol As String
Dim i As Integer




Set pt = Nothing
Set prange = Nothing
Set ptCache = Nothing
Set ws1 = Nothing
Set ws2 = Nothing


Set wb = Workbooks("td_metrics_excel3.xls")
Set ws1 = wb.Worksheets("Data_Page")



'For i = 1 To Worksheets.Count
'
'    If wb.Worksheets(i).Name <> "Data_Page" Then
'        wb.Worksheets(i).Name = "Pivot_Page"
'    End If
'
'Next i

wb.Worksheets.Add after:=wb.Sheets(wb.Sheets.Count), Count:=1

wb.Worksheets(wb.Worksheets.Count).Name = Chrt_Pg_Name

Set ws2 = wb.Worksheets(Chrt_Pg_Name)

lastRow = ws1.Cells(65536, 1).End(xlUp).Row
lastCol = ws1.Range("IV1").End(xlToLeft).Column

Set prange = ws1.Cells(1, 1).Resize(lastRow, lastCol)

Set ptCache = wb.PivotCaches.Add(xlDatabase, prange.Address)

Set pt = ptCache.CreatePivotTable(ws2.Cells(1, 1), p_tbl_name1)

pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01")

With pt.PivotFields("BG_USER_08")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
End With

pt.PivotFields("BG_DETECTION_DATE").LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, True, True)

With pt
    .ColumnGrand = False
    .RowGrand = False
End With

pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True
pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False

Set pt = Nothing

Set pt = ptCache.CreatePivotTable(ws2.Cells(40, 1), p_tbl_name2)

pt.AddFields RowFields:="BG_DETECTION_DATE", ColumnFields:="BG_PROJECT_DB"

With pt.PivotFields("BG_USER_08")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
End With

pt.PivotFields("BG_DETECTION_DATE").LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True, True, True)

With pt
    .ColumnGrand = False
    .RowGrand = False
End With

pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True
pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False


End Sub

Sub td_metrics_import()

Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim accapp As Access.Application
Dim wb As Workbook
Dim ws1 As Worksheet
Dim path As String
Dim i As Integer
Dim tblName As String

path = "C:\Documents and Settings\hendersr\My Documents\td_metrics.mdb"

tblName = "tbl_initial_td_select"


Set accapp = New Access.Application

accapp.OpenCurrentDatabase (path)
    accapp.Run ("qry_run")
accapp.Quit

Set db = Workspaces(0).OpenDatabase(path, ReadOnly:=False)

Set wb = Workbooks("td_metrics_excel3.xls")
Set ws1 = wb.Worksheets("Data_Page")

Application.DisplayAlerts = False

For i = 1 To wb.Charts.Count
    wb.Charts(i).Delete
Next i

ws1.Cells.ClearContents
    
For i = wb.Worksheets.Count To 1 Step -1
    
    If wb.Worksheets(i).Name <> "Data_Page" Then
        wb.Worksheets(i).Delete
    End If
    
Next i

Application.DisplayAlerts = True

Set rs = db.TableDefs(tblName).OpenRecordset

For i = 0 To rs.Fields.Count - 1
    
    If i = 0 Then
        ws1.Range("IV1").End(xlToLeft) = rs.Fields(i).Name
    Else
        ws1.Range("IV1").End(xlToLeft).Offset(0, 1) = rs.Fields(i).Name
    End If
    
Next i

ws1.Range("IV2").End(xlToLeft).CopyFromRecordset rs

End Sub

Sub create_graph()

    Charts.Add
    
    With ActiveChart
        
        .SetSourceData Source:=Sheets("Pivot_Page1").Cells(1, 1)
        .Location Where:=xlLocationAsNewSheet
        .PivotLayout.PivotTable.PivotFields("BG_PROJECT_DB").Orientation = xlHidden
        .PivotLayout.PivotTable.PivotFields("BG_DETECTION_DATE").Orientation = xlHidden
        .PivotLayout.PivotTable.PivotFields("BG_USER_01").Orientation = xlHidden
        
        With .PivotLayout.PivotTable.PivotFields("BG_SEVERITY")
            
            .Orientation = xlColumnField
            .Position = 1
        
        End With
         
        .PlotArea.Interior.ColorIndex = xlNone
    
    End With
            
End Sub

Sub main_prog()

    Call td_metrics_import
    Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
    'Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
    Call create_graph

End Sub

Set ws2 = wb.Worksheets(Chrt_Pg_Name)