I have a worksheet with a pivot table on Sheet1 and my data in Sheet2.
I need the pivot table to refresh if anything in D2 through D25 in Sheet2 changes. (or just column D altogether)
For the life of me I can't find a straight solution to this anywhere and it seems so simple, yet I seem to get some sort of error every time. Do I need to have them in the same worksheet?
Any help would be greatly appreciated. Thank you!
Try this with a Worksheet Activate change event:
Sheet1.PivotTables("name of pivot").RefreshTable
If I've helped U pls click on d *Add Reputation
How completely wrong is what I typed in?
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("Chart 1").RefreshTable
End Sub
I received this error:
run-time error '10004':
Method 'PivotTables' of object '_Worksheet' failed
have you tried putting it on WOrksheet Activate?
or you can add the On Error Resume Next statement on the first line.
The data is connected to an access database on SharePoint. The worksheet isn't actually opened or anything, so I don't know if activate would work? I need the pivot table to refresh because it is being displayed on the SharePoint page.
Is this what you mean?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
'ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Me.PivotTables(1).RefreshTable
End Sub
Nothing happens now.
then maybe you can use a Workbook change event instead of a worksheet change event
Workbook_SheetChange or Open
Do I have to put something in ByVal Target As Range?
No. just paste your code in the Workbook Sheet Change event:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sheet1.PivotTables("Chart 1").RefreshTable
End Sub
I don't know...
Last edited by JohnJK04; 07-31-2015 at 08:48 PM.
Got it. Thanks for your help!
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
End Sub
right click Sheet1 tab then View Code then on Project Explorer choose ThisWorkbook
then paste this code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sheet1.PivotTables("Chart 1").RefreshTable
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks