How to copy Headers of sheet where value is found in VBA?

Viewed 17

I have a macro that loops through IDs and outputs the location including sheet name and then entire row where the value is found. I also want to output the headers of the sheet where value is found above the entire row. How would I modify my macro to accomplish this?

 Sub findIDs2()
' findIDs2 Macro
' Once Parsed1 is run, run findIDs2 macro to output location and entire line where that Id is found.
'
    Application.ScreenUpdating = False
    Dim srcRng As Range, rng As Range, sAddr As String, fnd As Range, ws As Worksheet, x As Long: x = 1
    Dim rngWs As Range

Set srcRng = Sheets("IDs").Range("A2", Sheets("IDs").Range("A" & Rows.Count).End(xlUp))

For Each rng In srcRng
    For Each ws In Sheets
        If ws.Name <> "IDs" And ws.Name <> "Results" Then
            Set fnd = ws.Cells.Find(rng, LookIn:=xlValues, lookat:=xlPart)
            If Not fnd Is Nothing Then
                sAddr = fnd.Address
                Do
                    With Sheets("Results")
                        .Range("A" & x) = fnd
                        .Range("B" & x) = fnd.Address
                        .Range("C" & x) = ws.Name
                        
                         Set rngWs = Intersect(fnd.EntireRow, fnd.CurrentRegion)
                        rngWs.Copy
                         .Range("D" & x).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                        
                        x = x + 1
                    End With
                    Set fnd = ws.Cells.FindNext(fnd)
                Loop While fnd.Address <> sAddr
                sAddr = ""
            End If
        End If
    Next ws
Next rng

Application.ScreenUpdating = True

End Sub

0 Answers
Related