Search within Multiple Worksheets and Copy Rows to a New Worksheet using VBA

[starr]

Hi,

I am relatively new to VBA and would like to request for your kind help.

This is what I would like to do:
1. Given a search term eg: ABC (Name)
2. Search all worksheets in the workbook under the Name column that contains 'ABC'
3. Copy the rows found in multiple worksheets that contain 'ABC' to another new worksheet.

Thanks!

My current codes are below. It only works for 1 worksheet.

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    On Error GoTo Err_Execute
    LSearchRow = 2
    LCopyToRow = 3
    While Len(Sheets("Emp").Range("B" & CStr(LSearchRow)).Value) > 0
        If Sheets("Emp").Range("B" & CStr(LSearchRow)).Value = "ABC" Then
            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            'Paste row into Sheet1 in next row
            Sheets("Sheet1").Select
            Rows(CStr(LCopyToRow + 1) & ":" & CStr(LCopyToRow + 1)).Select
            ActiveSheet.Paste
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            'Go back to Sheet1 to continue searching
            Sheets("Emp").Select
        End If
        LSearchRow = LSearchRow + 1
    Wend
    Application.CutCopyMode = False
    Range("A3").Select
    MsgBox "All matching data has been copied."
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."

 

[starr]

Hi,

I did a search on the net again and found a solution to my post. Here is where I got my answer from. Hope this may help someone.

msofficesolved.com/general/Q_25560318-Excel-VBA-find-in-multiple-worksheets.jsp

 

[noboffinme]

HI

I followed this link & you need to sign up & give CC details to get the answer...

Any chance you can post the answer on the forum ?