Run-Time Error 1004 When Running Pivot Table Code

[Chergh]

Hey folks got a small problem with some code I'm using that results in the
following error message:

"Run-Time Error '1004':

The pivotTable field name is not valid. To create a pivottable report, you
must use data that is organized as a list with labeled columns. If you are
changing the name of a pivottable field, you must type a new name for the
field."

Here's the code:

####
####
Code Starts
####
####


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")


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

####
####
Code Ends
####
####



The code breaks on the line:

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

in the pt_td_metrics sub.

If I comment out one of the lines where I call the sub pt_td_metrics i.e.:

####
####

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

####
####

Everything works fine.

So anyone got any ideas?