How to Save cell selections after alternate dropdown selection

[Crystal_Wagner]

Good morning,

I am newish to excel and hoping for some assistance.

I am looking for a way to save the data selections made in certain cells, to refer back to after a different dropdown option in cell b2 is made. I will try to explain as best I can.

I want to be able to highlight cells in the proficient columns, that will save to that option chosing in b2.
I have written a macro that will clear the contents once i choose a different option in b2. However when i go back to a certain name, the previous selected cell options are not saved. i want to be able to go back and see options/cells highlighted that are associated with the different users in cell b2.

 

[Umma123]

Create a separate sheet to store highlighted cells' selection for each option in B2. Then adjust your macro to save the selected cell values before clearing them when a new option is chosen in B2

This is an outline of macro logic:

When new option is selected in B2, save current cell selections in the separately created sheet.
When switching back to a previously selected option in B2, retrieve and reapply saved selections.

Sub SaveSelections()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SavedSelections")

    ' Save the current selection when B2 changes
    Dim currentOption As String
    currentOption = Range("B2").Value

    ' Clear old data and save new data

    ws.Cells.Clear
    ws.Range("A1").Value = currentOption
    ws.Range("B1").Value = Selection.Address ' Save selected cells' address

End Sub

Sub RestoreSelections()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SavedSelections")
   
    ' Retrieve the selection when B2 changes back
    Dim savedOption As String
    savedOption = ws.Range("A1").Value
   
    If Range("B2").Value = savedOption Then
        Range(ws.Range("B1").Value).Select ' Reapply saved selections
    End If
End Sub

 

[Fluff]

@Umma123 when posting code please use code tags, How to Post Your VBA Code
I have add them for you this time.

 

[Crystal_Wagner]

Create a separate sheet to store highlighted cells' selection for each option in B2. Then adjust your macro to save the selected cell values before clearing them when a new option is chosen in B2

This is an outline of macro logic:

When new option is selected in B2, save current cell selections in the separately created sheet.
When switching back to a previously selected option in B2, retrieve and reapply saved selections.

Sub SaveSelections()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SavedSelections")

    ' Save the current selection when B2 changes
    Dim currentOption As String
    currentOption = Range("B2").Value

    ' Clear old data and save new data

    ws.Cells.Clear
    ws.Range("A1").Value = currentOption
    ws.Range("B1").Value = Selection.Address ' Save selected cells' address

End Sub

Sub RestoreSelections()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("SavedSelections")
  
    ' Retrieve the selection when B2 changes back
    Dim savedOption As String
    savedOption = ws.Range("A1").Value
  
    If Range("B2").Value = savedOption Then
        Range(ws.Range("B1").Value).Select ' Reapply saved selections
    End If
End Sub

Would this code need to be pasted into the current worksheet im in?
Or the newly created worksheet?