I've been using a macro that few of you guys have helped me with this evening. When using in a standard sub routine it works perfectly but each time it is used with the Private Sub Workbook_BeforeSave the second row with the word "Complete" in the B column does not get transferred to Sheet2. I would appreciate if any of you guys can help me out.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range, usedcell As Range
Dim lastrow As Long, x As Boolean
Set rng = Intersect(Sheets("Sheet1").UsedRange, _
Sheets("Sheet1").Range("B:B"))
For Each usedcell In rng
x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")
If x Then
lastrow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Rows(usedcell.Row).Cut Sheets("Sheet2").Rows(lastrow + 1)
End If
Next usedcell
End Sub
thanks
Matt
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng As Range, usedcell As Range
Dim lastrow As Long, x As Boolean
Set rng = Intersect(Sheets("Sheet1").UsedRange, _
Sheets("Sheet1").Range("B:B"))
For Each usedcell In rng
x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")
If x Then
lastrow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Rows(usedcell.Row).Cut Sheets("Sheet2").Rows(lastrow + 1)
End If
Next usedcell
End Sub
thanks
Matt