VBA Select Filter 2nd Drop Down List to have the same Value as the 1st

[eazy899]

Hi all,

I have a Drop Down List in Sheet 1, Cell F2 that has the values 1 to 5 in it for selection.
I have a 2nd Drop Down in Sheet 2, Cell F2 that also has the values 1 to 5 in it for selection.

Both Drop Downs use the same List for Data Validation.

I need to be able to filter the Drop Down on Sheet 1 for a value and have that same value applied to the dropdown in Sheet 2.

I previously did something similar with the below but cannot remember how for the life of me.

Private Sub Worksheet_Change(ByVal Target As Range)

Any help would be appreciated. Thanks.

 

[mart37]

When dropdowns in cel A1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Sheet2.[A1] = Sheets1.[A1]
End Sub

 

[eazy899]

When dropdowns in cel A1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Sheet2.[A1] = Sheets1.[A1]
End Sub

Thank you.

This is very helpful and worked when I wrote the code out as below but unfortunately resulted in another issue.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xWs As Worksheet

If Target.Address = "$G$2" Then


Worksheets("Data Sheet").Range("D2") = Target
Worksheets("Directorate Summary").Range("G2") = Target

End If

End Sub

The other issue is that:
1. I have 3 Sheets with 3 Drop Down Lists
2. Whenever I change the value in Sheet 1, I need the Value in Sheet 2 and 3 to match.
3. The above code enables me to change the value in Sheet 1 resulting in the same Value in Sheet 2 and 3.
4. However, when I put the same code into Sheet 2 or Sheet 3, it results in an error.

I.e. If the Macro in Sheet 1 changes the value in Sheet 2 and I have the same macro in Sheet 2, when I change the value in Sheet 2, I also need it to change the value in Sheet 1 but this activates the Macro in Sheet 1 by changing its value causing an error.

How do I rectify this so that changing the value in any of the 3 sheets results in the same value in the other 2 sheets?

 

[mart37]

The Worksheet_Change event do you need on all the three sheets.

 

[eazy899]

The Worksheet_Change event do you need on all the three sheets.

Yes, I need all 3 sheets.

All 3 sheets have a Drop Down List for Months 1 to 12.
I need the value in all 3 sheets to be the same no matter which sheet is used for the value.

 

[mart37]

Copy the macro to the other two sheets and replace the sheetname.

 

[eazy899]

Copy the macro to the other two sheets and replace the sheetname.

When I try that, it results in the error that causes Excel to crash.

I.e. When Sheet 1's Value Changes, it changes Sheet 2's value which is fine but then if Sheet 2 Value's change then it triggers the macro on Sheet 2 to make the Sheet 1 Value change.
That loop causes the crash.

 

[mart37]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xWs As Worksheet
If Target.Address = "$G$2" Then
     Application.EnableEvents = False
     Worksheets("Data Sheet").Range("D2") = Target
     Worksheets("Directorate Summary").Range("G2") = Target
     Application.EnableEvents = True
End If
End Sub

 

[eazy899]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xWs As Worksheet
If Target.Address = "$G$2" Then
     Application.EnableEvents = False
     Worksheets("Data Sheet").Range("D2") = Target
     Worksheets("Directorate Summary").Range("G2") = Target
     Application.EnableEvents = True
End If
End Sub

This works. Thank you so much.

Really appreciate the help.