I have a spreadsheet with values that I need to copy to another based on a set of rules. I started a macro but cannot figure out how to tell it to run for the number of rows in the spreadsheet. The only thing I do know is that somewhere in the loop I need to tell it the last row. I have attached my spreadsheet and code for review. Any help would be appreciated.
Dim statuswb As Workbook
Dim I As Integer
Dim NS As Integer
Dim LastRow As Integer
I = 2 'Beginning point of the loop
NS = 1 'Beginning point of load file for status
LastRow = Range("A65536").End(xlUp).Row + 1
Set statuswb = Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\my documents\Status" & CStr(Format(Now, "MMDDYYHHm")) & ".xls"
Do While Cells(I, 10).Value <> "Y" And Cells(I, 11) <> "Not in System"
If Cells(I, 1) = "Modify Status" Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "Y"
I = I + 1
NS = NS + 1