Sub or Function not defined

[happyhungarian]

Hi, I modified the following code to refresh a set of filters in a pivot table based upon the values that are placed in cell range. I thought this code could work but I'm getting a "Sub or Function not defined" error. Any idea where I could have messed up this code? Do you think this will ultimately work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'This line stops the worksheet updating on every change, 'it only updates when the cells are touched
If Intersect(Target, Range("D2:D6")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim FieldOrganization As PivotField
Dim FieldVP As PivotField
Dim FieldOrg_Function As PivotField
Dim FieldCC_Owner As PivotField
Dim FieldCost_Center As PivotField
Dim NewOrganization As String
Dim NewVP As String
Dim NewOrg_Function As String
Dim NewCC_Owner As String
Dim NewCost_Center As String


'Amend here to filter your data
Set pt = Worksheets("Travel").PivotTables("PivotTable1")
Set FieldRegion = pt.PivotFields("Organization")
Set FieldDept = pt.PivotFields("VP")
Set FieldDept = pt.PivotFields("Org Function")
Set FieldDept = pt.PivotFields("CC Owner")
Set FieldDept = pt.PivotFields("Cost Center")
NewOrganization = Worksheets("Trended P&L").Range("D2").Value
NewVP = Worksheets("Trended P&L").Range("D3").Value
NewOrg_Function = Worksheets("Trended P&L").Range("D4").Value
NewCC_Owner = Worksheets("Trended P&L").Range("D5").Value
NewCost_Center = Worksheets("Trended P&L").Range("D6").Value


'This updates and refreshes the PIVOT table
With pt
FieldRegion.ClearAllFilters
FieldRegion.CurrentPage = NewOrganization
FieldDept.ClearAllFilters
FieldDept.CurrentPage = NewVP
FieldDept.ClearAllFilters
FieldDept.CurrentPage = NewOrg_Function
FieldDept.ClearAllFilters
FieldDept.CurrentPage = NewCC_Owner
FieldDept.ClearAllFilters
FieldDept.CurrentPage = NewCost_Center
pt.RefreshTable
EndWith


EndSub

 

[Fluff]

Your missing a space in EndWith

 

[happyhungarian]

oh geeez! don't know what I was thinking... and i just realized I pasted the old/incomplete version of the code. Now that I made that change I'm getting a new error "Object variable or With block variable not set" and it's occurring at the section in RED font.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'This line stops the worksheet updating on every change, 'it only updates when the cells are touched
If Intersect(Target, Range("D2:D6")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim FieldOrganization As PivotField
Dim FieldVP As PivotField
Dim FieldOrg_Function As PivotField
Dim FieldCC_Owner As PivotField
Dim FieldCost_Center As PivotField
Dim NewOrganization As String
Dim NewVP As String
Dim NewOrg_Function As String
Dim NewCC_Owner As String
Dim NewCost_Center As String


'Amend here to filter your data
Set pt = Worksheets("Travel").PivotTables("PivotTable1")
Set FieldOrganization = pt.PivotFields("Organization")
Set FieldVP = pt.PivotFields("VP")
Set FieldOrg_Function = pt.PivotFields("Org Function")
Set FieldCC_Owner = pt.PivotFields("CC Owner")
Set FieldCost_Center = pt.PivotFields("Cost Center")
NewOrganization = Worksheets("Trended P&L").Range("D2").Value
NewVP = Worksheets("Trended P&L").Range("D3").Value
NewOrg_Function = Worksheets("Trended P&L").Range("D4").Value
NewCC_Owner = Worksheets("Trended P&L").Range("D5").Value
NewCost_Center = Worksheets("Trended P&L").Range("D6").Value


'This updates and refreshes the PIVOT table
With pt
FieldOrganization.ClearAllFilters
FieldOrganization.CurrentPage = NewOrganization
FieldVP.ClearAllFilters
FieldVP.CurrentPage = NewVP
FieldOrg_Function.ClearAllFilters
FieldOrg_Function.CurrentPage = NewOrg_Function
FieldCC_Owner.ClearAllFilters
FieldCC_Owner.CurrentPage = NewCC_Owner
FieldCost_Center.ClearAllFilters
FieldCost_Center.CurrentPage = NewCost_Center
pt.RefreshTable
End With


End Sub

 

[happyhungarian]

OK! Nevermind! I just fixed the issue with the "Object variable or With block variable not set" error! PHEW!!!! BUT!!! Now I have a separate question... for the section that tells the macro to trigger the pivot refresh:

'This line stops the worksheet updating on every change, 'it only updates when the cells are touched
If Intersect(Target, Range("D2:D6")) Is Nothing Then Exit Sub

If the D2:D6 range consists of a drop-down menu the macro is running when a new selection on the drop-down is made. You must first select a new item in the drop down and THEN select a different cell within the range to trigger the pivot refresh. Is there a way to modify the code so that as soon as a new value in the drop-down is selected that it triggers the refresh??? Apologies for random-ness of this thread!!!

 

[Fluff]

Change it to

Private Sub Worksheet_Change(ByVal Target As Range)

 

[happyhungarian]

Thank you!!!

 

[Fluff]

Your welcome