May 19 2021 03:08 AM
Hi,
There is probably a simple solution to this, but I just can't seem to get my head around it.
I have a drop-down box and I need to be able to identify all of the tabs that the data in the drop down appears. There are 135 tabs, they are all laid out exatly the same.
I have worked out how to count how many times it appears but I just need to know all of the tabs that it appears in, example attached
Thanks in advance
May 19 2021 03:37 AM
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Dim rng As Range
Dim r As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Range("C3:D" & Rows.Count).ClearContents
If Not Intersect(Range("B3"), Target) Is Nothing Then
If Range("B3").Value <> "" Then
r = 2
For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "Summary" Then
Set rng = wsh.Range("B:B").Find(What:=Range("B3").Value, LookAt:=xlWhole)
If Not rng Is Nothing Then
r = r + 1
Range("C" & r).Value = wsh.Name
Range("D" & r).Value = rng.Offset(0, 1).Value
End If
End If
Next wsh
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
See the attached version.
May 19 2021 04:00 AM
May 19 2021 04:19 AM
SolutionMay 19 2021 04:38 AM
May 19 2021 04:19 AM
Solution