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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You must log in or register to reply here.

Similar threads

C
  • Question Question
Pivot Table VBA
Replies
0
Views
230
chaddres
C
C
  • Solved
Pivot Table VBA
Replies
1
Views
374
Misca
M
T
  • Question Question
Clear FIlters of Pivot Table after Cell Value Changes
Replies
4
Views
438
Alex Blakenburg
A
ghull99
  • Question Question
Attempting VBA to refresh "Chart Title" based on Pivot Filters. Filter1 works in VBA, but title doesn't update based on filter2.
Replies
0
Views
49
ghull99
ghull99
D
  • Question Question
VBA code to filter out blank values in a pivot table
Replies
2
Views
584
dragontbone
D

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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