Need help with updating data range of pivot tables

Purple-Loyal

New Member
Joined
Sep 18, 2006
Messages
2
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Purple-Loyal

New Member
Joined
Sep 18, 2006
Messages
2
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.
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top