End macro if no filter results, but continue macro if results exist

[jl1066]

I'm looking for some help in the following code. I am filtering a set of data and depending on the results looking for either two things to happen: if there are results then copy and paste them into another workbook, but if there are no results I'm looking to have a message box appear, close the workbook and then end the Macro. But what's happening is if there are no results then the macro will continue with the code to copy and paste which I do not want.

Any help would be appreciated.

This is the code I'm using:

ws.Range("A14:AG14").AutoFilter field:=2, Criteria1:=""
Dim j As Integer
Dim r As Range
Set r = Range(Range("G15"), Range("G15").End(xlDown))

j = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
If j = 0 Then
result = MsgBox("No New Requisitions")
Workbooks("Filter Report.xlsx").Close savechanges:=False
End If
'End macro here if no results

'If there are filtered results copy the results and paste in the second workbook
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("D15:AE" & LR).SpecialCells(xlCellTypeVisible).Copy
Workbooks("Requisition Macro.xlsm").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.DisplayAlerts = False

 

[maabadi]

Try this:
if you turn off Application.EnableEvents or ScreenUpdating, Turn on Them Also Before Exit Sub

ws.Range("A14:AG14").AutoFilter field:=2, Criteria1:=""
Dim j As Integer
Dim r As Range
Set r = Range(Range("G15"), Range("G15").End(xlDown))

j = WorksheetFunction.Count(r.Cells.SpecialCells(xlCellTypeVisible))
If j = 0 Then
result = MsgBox("No New Requisitions")
Workbooks("Filter Report.xlsx").Close savechanges:=False
Exit Sub
End If
'End macro here if no results

'If there are filtered results copy the results and paste in the second workbook[/B]
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("D15:AE" & LR).SpecialCells(xlCellTypeVisible).Copy
Workbooks("Requisition Macro.xlsm").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.DisplayAlerts = False

 

[jl1066]

That worked! Thank you

 

[maabadi]

You're Welcome & Thanks for Feedback.