-
Apr 24th, 2018, 12:14 PM
#1
Thread Starter
New Member
Error in Worksheet_PivotTableUpdate
I'm trying to synchronize the filters of 2 pivot table(just from one way, change filters in pivot2 when filters in pivot1 are changed but not the opossite).
But I get this error when I try getting the sheet.
Excecution Time Erro 438
The pivot in this worksheet might be changed in VBA Code of the event "WorkbookOpen" so it might not run when this worksheet is active, this is the code:
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name <> "pivot1" Then Exit Sub
Dim st As Worksheet
Dim pivot1 As PivotTable
Dim pivot2 As PivotTable
Set st = Target.Worksheet
Set pivot2 = st.PivotTables("pivot2")
Set pivot1 = st.PivotTables("pivot1")
For Each pf In pivot1.PageFields
If pf.Name <> "Filter1" Then
pivot2.PageFields(pf.Name).CurrentPage = pf.CurrentPage
End If
Next
End Sub
Last edited by kadzu; Apr 24th, 2018 at 03:50 PM.
-
Apr 25th, 2018, 02:07 AM
#2
Re: Error in Worksheet_PivotTableUpdate
"Target" doesn't have a Worksheet property. You can use:
Code:
Set st = activesheet
-
Apr 25th, 2018, 07:34 AM
#3
Thread Starter
New Member
Re: Error in Worksheet_PivotTableUpdate
But what is activeworksheet? If it is the worksheet currently been seen then it wont work because the pivot table will be changed in the WorkbookOpen and the sheet with this pivot is not the first sheet that shows up when the file is opened.
-
Apr 25th, 2018, 08:41 AM
#4
Re: Error in Worksheet_PivotTableUpdate
If it's this event:
Code:
Worksheet_PivotTableUpdate
then the active sheet is the one that contains the pivot table that got updated. Would that not be want you'd want it to respond to?
-
Apr 25th, 2018, 04:08 PM
#5
Re: Error in Worksheet_PivotTableUpdate
But what is activeworksheet?
i would guess that a pivottable would have a parent object, which would be the worksheet
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 26th, 2018, 05:02 AM
#6
Re: Error in Worksheet_PivotTableUpdate
Correct. You can look at the "Target.Parent.Name" to confirm which sheet.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|