tsebastian03
New Member
- Joined
- Mar 6, 2013
- Messages
- 9
Hi All,
I have used the following macro to create a report based on criteria. Currently, it outputs the row 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. When the rows are outputted to a new sheet, I would like to have defined headers so the output starts in Row 2 and I can have the same header for all outputs. These headers are Row 1 in the sheet labeled "COM".
3. 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 WEBS for the same search criteria.
Could I get some help on how to revise the code to achieve this? The original code is below:
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
I have used the following macro to create a report based on criteria. Currently, it outputs the row 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. When the rows are outputted to a new sheet, I would like to have defined headers so the output starts in Row 2 and I can have the same header for all outputs. These headers are Row 1 in the sheet labeled "COM".
3. 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 WEBS for the same search criteria.
Could I get some help on how to revise the code to achieve this? The original code is below:
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