On Error, Message, Exit

[Helmut]

Hi, In the following, I need to add:
MsgBox "No #N/A cells found. Nothing to copy. Finished."
--and when clicking OK, close the workbook without saving.
before exiting in this statement: If Not IsError(.Cells(lr, "r")) Then Exit
Sub

-------------------------------------------------------------------
' copy value in columns CDE #N\A to Employees.xlsx

Set swb = ActiveWorkbook 'IF open
Set dwb = Workbooks("Employees.xlsx") 'If open
dlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row + 1
'MsgBox dlr
With swb.Sheets(1)
lr = .Cells(Rows.Count, "a").End(xlUp).Row

If Not IsError(.Cells(lr, "r")) Then Exit Sub

..Range("a1:r" & lr).AutoFilter field:=18, Criteria1:="#N/A"
..Range("e2:e" & lr).SpecialCells(xlCellTypeVisible).Copy
dwb.Sheets(1).Range("a" & dlr)
..Range("c2:d" & lr).SpecialCells(xlCellTypeVisible).Copy
dwb.Sheets(1).Range("b" & dlr)
..Range("a1:r" & lr).AutoFilter
nlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox nlr

'print range
dwb.Activate
Sheets(1).PageSetup.PrintArea = Range(Cells(dlr, "a"), Cells(nlr,
"c")).Address
ActiveWindow.SelectedSheets.PrintOut
End With

 

[cht13er]

Hi, In the following, I need to add:
MsgBox "No #N/A cells found. Nothing to copy. Finished."
--and when clicking OK, close the workbook without saving.
before exiting in this statement:  If Not IsError(.Cells(lr, "r")) Then Exit
Sub

-------------------------------------------------------------------
' copy value in columns CDE #N\A to Employees.xlsx

Set swb = ActiveWorkbook 'IF open
Set dwb = Workbooks("Employees.xlsx") 'If open
dlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row + 1
'MsgBox dlr
With swb.Sheets(1)
lr = .Cells(Rows.Count, "a").End(xlUp).Row

 If Not IsError(.Cells(lr, "r")) Then Exit Sub

.Range("a1:r" & lr).AutoFilter field:=18, Criteria1:="#N/A"
.Range("e2:e" & lr).SpecialCells(xlCellTypeVisible).Copy
dwb.Sheets(1).Range("a" & dlr)
.Range("c2:d" & lr).SpecialCells(xlCellTypeVisible).Copy
dwb.Sheets(1).Range("b" & dlr)
.Range("a1:r" & lr).AutoFilter
nlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row
'MsgBox nlr

'print range
dwb.Activate
Sheets(1).PageSetup.PrintArea = Range(Cells(dlr, "a"), Cells(nlr,
"c")).Address
ActiveWindow.SelectedSheets.PrintOut
End With

This should be what you're looking for:

If Not IsError(.Cells(lr, "r")) Then
Call MsgBox("No #N/A cells found. Nothing to copy.", vbokonly,
"Finished")
Exit Sub
End If


Chris