PDA

View Full Version : Pivot chart - Auto change Data source



justdream
04-28-2013, 07:20 AM
Dears,

I've workbook contains more than 10 Tabs each has different Pivot chart
Each Day when I get new RawData
I go to each Pivot table and do manually change Data source

Is there smart way so Data could be automatically updated?

Nec11
04-28-2013, 08:30 AM
hello,
I had the same problem in the past
Now in the surce table of the PT I keep the data format cell in number and for each PT I use the following VBA

Sub Button_PT()
Sheet5.Unprotect
AllWorksheetPivots 'refresh PT
PT_Change_value 'Change date
Sheet5.Protect
End Sub

'refresh PT
Sub AllWorksheetPivots()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub

'change value in today's date
Sub PT_Change_value()
'in "B3" I keep the date of today() but format cell in number
'in "C3"=anything
If Sheet5.Range("B3") <> Sheet5.Range("C3") Then
'Cell "B7" is the date selection of the PT
Sheet5.Range("B7").Value = Sheet5.Range("B3")
End If
End Sub

I hope it helped you

justdream
04-28-2013, 11:10 AM
Thanks for sharing,
what's sheet5 indicates in your code?

Nec11
04-28-2013, 12:43 PM
in sheet5 is my PT

you have to change the sheet numbering accourding to your case