Hello everyone,
I have a macro which builds a report to be sent out daily, however some days there are no updates in certain categories.
The macro copies over certain template lines from a template report, then filters the formatted data sheet and copies the rows over to a report
the problem is that certain days the filter returns no rows, so I put in a lengthy if statement
Here is the code I am currently using, I would like to simplify it (the entire macro would run this about 20 times over for different sections)
Sub iferrorsection()
Sheets("worklist formatted").Activate
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.Count - 1
Sheets("worklist formatted").Range("A1:R1000").AutoFilter Field:=18, Criteria1:= _
"West Region"
'filter formatted data and copy paste below template
Sheets("worklist formatted").Range("A1:R1000").AutoFilter Field:=6, Criteria1:= _
"test received"
matched_criteria = 0
check_row = 0
While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
Else
matched_criteria = matched_criteria + 1
End If
Wend
If row_count = check_row Then
Sheets("West Region").Range("A4").Value = "no tests were received"
Sheets("West Region").Range("A4:I4").Merge
Else
Sheets("worklist formatted").Range("A2:I500").SpecialCells(xlCellTypeVisible).Copy
Sheets("West Region").Range("A4").PasteSpecial xlPasteValues
End If
End Sub
any ideas???
Thanks
Bookmarks