VBA in Excel for Pivot Table Selection (want to use a variable)
 

I've got a listbox with a location selection which I'd like the user
to use. The user will select the a specific location name and this
should access the pivot table (actually it's a cube) location field
and make the applicable selection in the pivot table.

This is what I have - not sure if I'm on the right track.

Sub LocListbox_Change()
'Select Location Name from Cube for Loc G&E
Dim LocNum As Int
Dim LocName As String
Application.ScreenUpdating = False
LocNum = Worksheets("ByLocGraphsExceptions").Range("R1")
Sheets("Tables").Activate
LocName = WorksheetFunction.VLookup(LocNum, Range("A1:B101"), 2,
False)
Worksheets("LocGeData").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("[PickupLocationCode]").
_
CurrentPageName = "[PickupLocationCode].[All].LocName"
Worksheets("ByLocGraphsExceptions").Select
Application.ScreenUpdating = True

End Sub

So the idea is that I am trying to create a variable LocName from a
reference table I have which returns a string inline with how the
pivot table has the locations listed, then I want to select this
location from the pivot table using that variable name but how do i
make this work? Any ideas??

thanks

VBA in Excel for Pivot Table Selection (want to use a variable)
 

On Jul 15, 7:40 pm, Vaughny wrote:
I've got a listbox with a location selection which I'd like the user
to use. The user will select the a specific location name and this
should access the pivot table (actually it's a cube) location field
and make the applicable selection in the pivot table.

This is what I have - not sure if I'm on the right track.

Sub LocListbox_Change()
'Select Location Name from Cube for Loc G&E
Dim LocNum As Int
Dim LocName As String
Application.ScreenUpdating = False
LocNum = Worksheets("ByLocGraphsExceptions").Range("R1")
Sheets("Tables").Activate
LocName = WorksheetFunction.VLookup(LocNum, Range("A1:B101"), 2,
False)
Worksheets("LocGeData").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("[PickupLocationCode]").
_
CurrentPageName = "[PickupLocationCode].[All].LocName"
Worksheets("ByLocGraphsExceptions").Select
Application.ScreenUpdating = True

End Sub

So the idea is that I am trying to create a variable LocName from a
reference table I have which returns a string inline with how the
pivot table has the locations listed, then I want to select this
location from the pivot table using that variable name but how do i
make this work? Any ideas??

thanks

Correction to my post:

"Dim LocNum As Int "

should read: "Dim LocNum As Integer" - sorry I was typing too fast.

VBA in Excel for Pivot Table Selection (want to use a variable)
 

On Jul 15, 7:55 pm, Vaughny wrote:
On Jul 15, 7:40 pm, Vaughny wrote:





I've got a listbox with a location selection which I'd like the user
to use. The user will select the a specific location name and this
should access the pivot table (actually it's a cube) location field
and make the applicable selection in the pivot table.

This is what I have - not sure if I'm on the right track.

Sub LocListbox_Change()
'Select Location Name from Cube for Loc G&E
Dim LocNum As Int
Dim LocName As String
Application.ScreenUpdating = False
LocNum = Worksheets("ByLocGraphsExceptions").Range("R1")
Sheets("Tables").Activate
LocName = WorksheetFunction.VLookup(LocNum, Range("A1:B101"), 2,
False)
Worksheets("LocGeData").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields ("[PickupLocationCode]").
_
CurrentPageName = "[PickupLocationCode].[All].LocName"
Worksheets("ByLocGraphsExceptions").Select
Application.ScreenUpdating = True

End Sub

So the idea is that I am trying to create a variable LocName from a
reference table I have which returns a string inline with how the
pivot table has the locations listed, then I want to select this
location from the pivot table using that variable name but how do i
make this work? Any ideas??

thanks

Correction to my post:

"Dim LocNum As Int "

should read: "Dim LocNum As Integer" - sorry I was typing too fast.- Hide quoted text -

- Show quoted text -

Any ideas? I'm pretty stuck here.

Thx.