Updating Selection Lists with Pivot

G

Grubsing

New Member
Joined
Mar 13, 2015
Messages
4
hi all,

I have an excel workbook in which I want to automatically update selection lists based on the user who is logging in and which selection is made from a few dropdown boxes. The user does the following steps:

1. Login (the username is saved to named range called cCurrent_User in the sheet "Login") --> depending on who is logging in, the selection lists in the "set-up" sheet shows whatever is available in terms of data for this user. In order to do this, the pivot table called "ptResponsible" in the sheet "PivotTable" should update
2. Next, the user will select a customer segment, a country and the customer name from drop down lists in the sheet "Set-up" (so the login username is saved to a different worksheet than where the other named ranges/dropdown boxes are) --> based on e.g. the chosen customer segment, the available countries should update etc. For each, a pivottable is included in the sheet "PivotTable" to update.
3. I have written the following code, which worked perfectly until I added the login feature and code to take the username into consideration in the selection lists to the workbook. Below you will find my code, I feel it has to do something with the fact that the Login named range is not on the same worksheet as the other named ranges but I don't know what to do to change it around. I have marked what I have added/changed to the previously working version of the code. The error I get is: Range of object worksheet failed. I'm hoping someone can help me out! Sorry for this long story...

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Range("cCustomerSegment").Value) Then 'rTargetAddress = Range("cCustomerName").Address And
Range("cCustomerSegment") = "All"
End If

If IsEmpty(Range("cCountry").Value) Then 'rTargetAddress = Range("cCustomerName").Address And
Range("cCountry") = "All"
End If

If IsEmpty(Range("cCustomer").Value) Then 'rTargetAddress = Range("cCustomerName").Address And
Range("cCustomer") = "All"
End If

If Target.Address = Range("cCurrent_User").Address Then
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Responsible").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Responsible").CurrentPage = Range("cCurrent_User").Value
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Responsible").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Responsible").CurrentPage = Range("cCurrent_User").Value
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Responsible").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Responsible").CurrentPage = Range("cCurrent_User").Value
End If
If Target.Address = Range("cCustomerSegment").Address Then
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Customer Segment Juan").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Customer Segment Juan").CurrentPage = Range("cCustomerSegment").Value
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Customer Segment Juan").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Customer Segment Juan").CurrentPage = Range("cCustomerSegment").Value
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Customer Segment Juan").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Customer Segment Juan").CurrentPage = Range("cCustomerSegment").Value
End If
If Target.Address = Range("cCountry").Address Then
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Country").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Country").CurrentPage = Range("cCountry").Value
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Country").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Country").CurrentPage = Range("cCountry").Value
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Country").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Country").CurrentPage = Range("cCountry").Value
End If
If Target.Address = Range("cCustomer").Address Then
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Customer Code and Name").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Customer Code and Name").CurrentPage = Range("cCustomer").Value
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Customer Code and Name").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Customer Code and Name").CurrentPage = Range("cCustomer").Value
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Customer Code and Name").ClearAllFilters
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Customer Code and Name").CurrentPage = Range("cCustomer").Value
End If
'If Target.Address = Range("cCustomerSegment").Address And IsEmpty(Target) Then
'Target.Value = "All"
'End If

Application.ScreenUpdating = False

If Target.Adress = Range("cCurrent_User").Adress Or Target.Address = Range("cCustomerSegment").Address Or Target.Address = Range("cCountry").Address Or Target.Address = Range("cCustomer").Address Then
'Update table ccCurrentUser in Selection Lists
Worksheets("Selection Lists").Range("O4").ListObject.DataBodyRange.Rows.Delete
Worksheets("PivotTable").Activate
Worksheets("PivotTable").PivotTables("ptResponsible").PivotFields("Responsible").DataRange.Select
Selection.Copy
Worksheets("Selection Lists").Activate
Worksheets("Selection Lists").Range("O4") = "All"
ActiveSheet.Paste Destination:=Worksheets("Selection Lists").Range("O5")

' Update table ccCustomerSegment in Selection Lists
Worksheets("Selection Lists").Range("I4").ListObject.DataBodyRange.Rows.Delete
Worksheets("PivotTable").Activate
Worksheets("PivotTable").PivotTables("ptCustomerSegment").PivotFields("Customer Segment Juan").DataRange.Select
Selection.Copy
Worksheets("Selection Lists").Activate
Worksheets("Selection Lists").Range("I4") = "All"
ActiveSheet.Paste Destination:=Worksheets("Selection Lists").Range("I5")


' Update table ccCountry in Selection Lists
Worksheets("Selection Lists").Range("K4").ListObject.DataBodyRange.Rows.Delete
Worksheets("PivotTable").Activate
Worksheets("PivotTable").PivotTables("ptCountry").PivotFields("Country").DataRange.Select
Selection.Copy
Worksheets("Selection Lists").Activate
Worksheets("Selection Lists").Range("K4") = "All"
ActiveSheet.Paste Destination:=Worksheets("Selection Lists").Range("K5")
' Update table ccCustomer in Selection Lists
Worksheets("Selection Lists").Range("M4").ListObject.DataBodyRange.Rows.Delete
Worksheets("PivotTable").Activate
Worksheets("PivotTable").PivotTables("ptCustomer").PivotFields("Customer Code and Name").DataRange.Select
Selection.Copy
Worksheets("Selection Lists").Activate
Worksheets("Selection Lists").Range("M4") = "All"
ActiveSheet.Paste Destination:=Worksheets("Selection Lists").Range("M5")
Worksheets("Set-Up").Activate
End If
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You must log in or register to reply here.

Similar threads

F
  • Solved
Problems filtering a Pivot Table with VBA
Replies
2
Views
163
Domenic
D
A
  • Question
Pivot Table VBA Help
Replies
0
Views
419
ashley1984
A
wsnyder
  • Question
Update Pivot Filters from Cell Values
Replies
1
Views
441
DanteAmor
DanteAmor
M
  • Question
Select pivot table and then macro filter
Replies
0
Views
656
Mark Milan
M
C
  • Question
Creating a button to select from a Pivot table filter
Replies
1
Views
425
Micron
M

Forum statistics

Threads
1,214,523
Messages
6,120,034
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top