Need help with updating data range of pivot tables

[Purple-Loyal]

Hi guys,

I am working on this project and ran into this problems with pivottable updating. Your helps are really appreciated.

The report receives new data every month in excel format and i need a macro to update 2 pivot tables so the newly added data will be included into the source data range of the pivot tables. Both tables show year and month in columns, and performance in rows. By default, only the last 3 month of data should be displayed.

My macro has no problem of updating the 1st pivot table, means expanded source data range, set the 4th latest month pivotitem to be invisible, and set the latest month pivotitem (newly added) to be visible.

However, when it gets to the 2nd pivot table, it couldn't update the source data range and caused the problem of exchanging display information later.

Here is my code:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= "HOA!R1C1:R" & Sheets(hoa_sh).UsedRange.Rows.Count & "C32"

ActiveSheet.PivotTables("PivotTable1").RefreshTable
ActiveSheet.PivotTables("PivotTable2").RefreshTable

.................code for updating pivottable 1 .................

' Updates the fiscal year list in pivot table 2

x = 1
Do While x <= ActiveSheet.PivotTables("PivotTable4").PivotFields("Fiscal Yr").PivotItems.Count
Select Case UCase(ActiveSheet.PivotTables("PivotTable4").PivotFields("Fiscal Yr").PivotItems(x).Name)
Case UCase(tim_yr(1)), UCase(tim_yr(2))
ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal Yr").PivotItems(x).Visible = True
Case Else
ActiveSheet.PivotTables("PivotTable2").PivotFields("Fiscal Yr").PivotItems(x).Visible = False
End Select
x = x + 1
Loop


' Updates the displaying list of Months in pivot table 2
With ActiveSheet.PivotTables("PivotTable2").PivotFields("MONTH")
MsgBox "set " & .PivotItems(35).Name & "=" & .PivotItems(35).Visible & " to be true"
.PivotItems(35).Visible = True ' the added month
End With


Since the 2nd pivot table didn't expand its source data range, it does add the newly added month into the pivotitems list which is 35.

Could someone tell me how to reset the source data range for the 2nd pivot table? Thanks in advance,


Bing

 

[Purple-Loyal]

Sorry, at the 2nd paragraph from the bottom, I really mean:

Since the 2nd pivot table didn't expand its source data range, it does NOT add the newly added month into the pivotitems list which is 35.