compare value in cell and copy cells A through S of same row to a new worksheet
 

I'm trying to get this routine to work, but it's failing on the lin
between the ***. I get a runtime error "9" subscript out of rang
error. I'm trying to find cells containing "late" and copy tha
row,cells A-S into a new page called CertReport. Thanks for an
input.


Dim x As Long, lr As Long, lr2 As Long
lr
Application.WorksheetFunction.CountA(Sheets("CRTDA TQ1").Range("A:A"))
For x = 1 To lr
***lr2
Application.WorksheetFunction.CountA(Sheets("CertR eport").Range("A:A")
+ 1***
If Sheets("CRTDATQ1").Cells(x, 11).Value = "late" Then
Sheets("CRTDATQ1").Cells(x, 11).Range("A:S").Copy
Sheets("CertReport").Select
Cells(lr2, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Nex

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

compare value in cell and copy cells A through S of same row to a new worksheet
 

I figured out he cause of the error. I had to change the name of shee
1 to CertReport. It's still not copying cells to new page...Any Ideas

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com