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

[Vaughny]

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

 

[Vaughny]

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.

 

[Vaughny]

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.