I am getting this runtime error 1004 when my code is trying to populate a pivot table. The template with the pivot table on it is currently open. The stop point is highlighted in green. strFld = Facility, li1stGrp = 3, li2ndGrp=2? Any help is appreciated. Tom
Code:
Public Sub RS_SvcPivot(liCl As Long, liRPd As Long, strTitl As String, strSubTitl As String, li1stGrp As Long, li2ndGrp As Long)
Dim strSQL As String
Dim rstDat As Recordset
Dim iRec As Integer
Dim iRw As Integer
Dim strFld As String
' Set SubGroup Titles
With goXL.ActiveSheet
.Cells(1, 107).Value = (GetSubName(li1stGrp))
.Cells(1, 108).Value = (GetSubName(li2ndGrp))
End With
' Get SvcSummary Data
strSQL = "SELECT uci,ptype,grpdsc1,grpdsc2,rcatdesc,svcyr,monasdt,Sum(amt) as tamt " & _
"FROM PROC_RptSrc_SvcPivot " & _
"GROUP BY uci,ptype,grpdsc1,grpdsc2,rcatdesc,svcyr,monasdt " & _
"ORDER BY ptype;"
Set rstDat = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
iRw = 2
If Not rstDat.EOF Then
With rstDat
.MoveLast
.MoveFirst
End With
For iRec = 1 To rstDat.RecordCount
With goXL.ActiveSheet
.Cells(iRw, 105).Value = (rstDat![UCI])
.Cells(iRw, 106).Value = (rstDat![ptype])
.Cells(iRw, 107).Value = (rstDat![grpdsc1])
.Cells(iRw, 108).Value = (rstDat![grpdsc2])
.Cells(iRw, 109).Value = (rstDat![rcatdesc])
.Cells(iRw, 110).Value = (rstDat![svcyr])
.Cells(iRw, 111).Value = (rstDat![monasdt])
.Cells(iRw, 112).Value = (rstDat![tamt])
End With
iRw = iRw + 1
rstDat.MoveNext
Next iRec
'End If Moved this end if to end of routine 7/8/2013 TD
rstDat.Close
Set rstDat = Nothing
' Reset Data Name Range
ActiveWorkbook.Names.Add Name:="SvcData", RefersToR1C1:="=Svc_Summary!R1C105:R" & (iRw - 1) & "C112"
' Refresh
With goXL.ActiveSheet
.Range("B9").Select
'.PivotTables("PivotTable1").PivotCache.Refresh
'Caused error on pivot chart commented out 07/08/2013
End With
' Configure Sub Groups for Filtering
If (li1stGrp = 1) Then ' No First Filter
If (li2ndGrp = 1) Then ' No First or Second Filter
' Do nothing
Else ' Second Filter Only
strFld = (GetSubName(li2ndGrp))
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
.Orientation = xlPageField
.Position = 1
End With
End If
Else ' First Filter
If (li2ndGrp = 1) Then ' No Second Filter, First Only
strFld = (GetSubName(li1stGrp))
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
.Orientation = xlPageField
.Position = 1
End With
Else ' Both First and Second Filter
strFld = (GetSubName(li1stGrp))
[GREEN] With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld) [/GREEN]
.Orientation = xlPageField
.Position = 1
End With
strFld = (GetSubName(li2ndGrp))
With ActiveSheet.PivotTables("PivotTable1").PivotFields(strFld)
.Orientation = xlPageField
.Position = 1
End With
End If
End If
End If 'Added 7/8/2013 TD
Application.CommandBars("PivotTable").Visible = False
End Sub