Here's some code that looks at the dropdown list value as though it is placed in cell K1 on the sheet titled "Dashboard". Change "K1" in this code to whatever or wherever you intend to extract the search value. This code should loop through all sheets from the 3rd to the 2nd from the end, looking for the name chosen (and placed in K1 for lack of a better/correct place), and adding any line with the that name to the dashboard sheet.
Sub test()
On Error GoTo Errhndlr
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Rcnt = 7
Sheets("Dashboard").Range("A7:K50").ClearContents
For i = 3 To Sheets.Count - 1
SrchVlu = Sheets("Dashboard").Range("K1").Value
Sheets(i).Select
Range("A1").Select
lfnd = vbNullString
For t = 2 To WorksheetFunction.CountA(ActiveSheet.Range("A:A"))
On Error GoTo nxti
Set Found = Cells.Find(What:=SrchVlu, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Found = "" Then
GoTo nxti
Else
Cells.Find(What:=SrchVlu, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End If
If ActiveCell.Address = lfnd Or ActiveCell.Row = 1 Then
GoTo nxti
End If
lc = ActiveCell.Row
lfnd = ActiveCell.Address
Sheets("Dashboard").Range("A" & Rcnt & ":K" & Rcnt).Value = ActiveSheet.Range("A" & lc & ":K" & lc).Value
Rcnt = Rcnt + 1
Next t
nxti:
Next i
Errhndlr:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Sheets("Dashboard").Select
Exit Sub
End Sub
Bookmarks