nakedbamboo
New Member
- Joined
- Jun 8, 2015
- Messages
- 9
I am trying to refresh a pivot table after adding new monthly data to the source. The refresh works fine, but when I try to modify the dates shown with a filter it throws an Application-defined or object-defined error.
I fought it for a couple of days and then just decided to create the pivot table from scratch. When I create a new pivot table with all the data, and then I use the exact same line to do an xlDateBetween filter, it works fine. So I thought there must be something wrong with the original table. I deleted it and kept the table I just successfully created and filtered as the new table. I then went back to the effort of just refreshing it, but it has the same error on the filter line.
I have posted my code below. The commented lines are the ones that were used to create a new table and everything worked fine. I then commented them, used that same table with the same filter code, and got the error on the last line of code below. The refresh code worked, so I know it is pointing to the correct table.
Thanks for any help.
I fought it for a couple of days and then just decided to create the pivot table from scratch. When I create a new pivot table with all the data, and then I use the exact same line to do an xlDateBetween filter, it works fine. So I thought there must be something wrong with the original table. I deleted it and kept the table I just successfully created and filtered as the new table. I then went back to the effort of just refreshing it, but it has the same error on the filter line.
I have posted my code below. The commented lines are the ones that were used to create a new table and everything worked fine. I then commented them, used that same table with the same filter code, and got the error on the last line of code below. The refresh code worked, so I know it is pointing to the correct table.
Thanks for any help.
Code:
monthVal = DateSerial(Year(Date), Month(Date) - 1, 1)
monthVal2 = DateSerial(Year(Date), Month(Date) - 6, 1)
wbAdjCDL.Activate
RCnt1 = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(3, 1), Cells(RCnt1 - 1, 1)).Copy
wbAnalCDL.Sheets("New CDL Data").Activate
RCnt2 = Cells(Rows.Count, 1).End(xlUp).Row
Cells(RCnt2 + 1, 2).PasteSpecial Paste:=xlValues
wbAdjCDL.Activate
Range(Cells(3, 2), Cells(RCnt1 - 1, 4)).Copy
wbAnalCDL.Sheets("New CDL Data").Activate
Cells(RCnt2 + 1, 5).PasteSpecial Paste:=xlValues
RCnt3 = Cells(Rows.Count, 2).End(xlUp).Row
Cells(RCnt2 + 1, 1) = monthVal
Cells(RCnt2 + 1, 1).Copy
Range(Cells(RCnt2 + 2, 1), Cells(RCnt3, 1)).PasteSpecial
Cells(RCnt2, 1).Copy
Range("A:A").PasteSpecial Paste:=xlPasteFormats
Range(Cells(RCnt2, 3), Cells(RCnt3, 3)).FillDown
Range(Cells(RCnt2, 4), Cells(RCnt3, 4)).FillDown
RCnt = Cells(Rows.Count, 1).End(xlUp).Row
' Sheets.Add.Name = "Pivot"
Sheets("Pivot").Select
' ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="New CDL Data!R1C1:R" & RCnt & "6C7", Version:=xlPivotTableVersion15). _
' CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15
Set pvt = ActiveSheet.PivotTables("PivotTable2")
' pvt.PivotFields("EOM Month").Orientation = xlColumnField
' pvt.AddDataField ActiveSheet.PivotTables("PivotTable2").PivotFields("Total Posted Exp Adj Amt"), "Sum of Total Posted Exp Adj Amt", xlSum
' pvt.PivotFields("Adj Type").Orientation = xlRowField
' pvt.PivotFields("Adjustment Code").Orientation = xlRowField
' pvt.PivotFields("Adj Code Description").Orientation = xlRowField
' pvt.RowAxisLayout xlTabularRow
' With pvt
' For Each pvtFld In .PivotFields
' pvtFld.Subtotals(1) = True
' pvtFld.Subtotals(1) = False
' Next pvtFld
' End With
' With pvt.PivotFields("EOM Month")
' .PivotItems("(blank)").Visible = False
' End With
' pvt.PivotFields("EOM Month").AutoSort xlDescending, "EOM Month"
pvt.PivotCache.Refresh
pvt.PivotFields("EOM Month").PivotFilters.Add2 Type:=xlDateBetween, Value1:=monthVal2, Value2:=monthVal