Updating Selection Lists with Pivot

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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Watch MrExcel Video

Forum statistics

Threads
1,114,580
Messages
5,548,866
Members
410,881
Latest member
toonces
Top