Revise marco so searched output is in new workbook plus search 2 sheets, add headers

[tseb3]

Hi All,


I have used the following macro to create a report based on criteria. Currently, it outputs the results to a sheet labeled "Report". I need to revise the code to add some additional aspects.


1. I would like the output to be to a new workbook.


2. The macro as written only searches the sheet "HOTLINE" and returns the rows that have the search criteria "Unqualified Lead" in Column A. I would like for it to search 2 sheets: HOTLINE and WEBINAR for the same search criteria.

3. I would like for the output to be in a new workbook with two sheets: HOTLINE AND WEBINAR and use the headers from the original tabs for the outputted rows



Could I get some help on how to revise the code to achieve this? The original code is below:

EDIT: I've attached a sample file!

Sub UnqualifiedLeads()
'Updated 4-21-2020
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("HOTLINE").UsedRange.Rows.Count
    J = Worksheets("Report").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Report").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("HOTLINE").Range("A1:A" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Unqualified Lead" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Report").Range("A" & J + 1)
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "Unqualified Lead Report Complete!", vbExclamation
    Sheets("Report").Select
End Sub