The routine below sorts a spreadsheet by a certain criteria, then copies the visible cells and pastes into another spreadsheet. I've added (don't know how well) a section that will pop up a message if no valid criteria exists to sort. The copied cells will be incorporated into a larger report, so the example code below is for only the first step of 9 different steps, each resulting in a copy/paste.
I'd like to be able to do two additional functions:
1) For each selection I copy/paste, add Text either before or after saying something like "Step 1 Completed: Results are Below" (or "Above")
2) For a selection resulting in invalid criteria, don't copy anything, instead paste something like "Step 1 Completed: No results were found for this selection"
Here's the code; I've tried to note what each section does, and would appreciate any help cleaning it up and augmenting the functionality.
Thanks.
Sub Step_1()
Dim DstWkb As Workbook
Dim Rng As Range
Dim RngEnd As Range
Dim cell As Range
Set Rng = Selection
Set DstWkb = Workbooks("APR Checklist_working.xls")
'Restrain the filter to cells from A1 to the last entry in column W
With DstWkb.Worksheets("Data")
Set Rng = .Range("A1:W1")
Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, .Range(Rng, RngEnd))
End With
'Filter the data using column M
Rng.EntireRow.Autofilter Field:=13, Criteria1:=("0")
'Display a message if no valid criteria exists
If Rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
MsgBox "Good Work! You have no Temporary FTEs!"
End If
'Trap the error if there were no matches
On Error Resume Next
'Copy and paste only the filtered data
Range("J1").Activate
Rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=DstWkb.Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0)
'Non-working attempt to paste a message if no valid criteria exists
If Rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
PasteSpecial ("Step 1 Completed: No Temporary FTEs found")
End If
'Clear the error if there was one
Err.Clear
'Return error control back to the system
On Error GoTo 0
End Sub
Bookmarks