Slight Problem with Private Sub Workbook_BeforeSave Macro

[Matt]

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

[Jay Petrulis]

Hi Matt,

I don't think this will be a problem, but ensure that you placed the "before save" routine in the ThisWorkbook module, and not a regular module.

If the above is OK, then possibly you can remove the sub and place it in a regular module and call it from the "before save" event.


Private Sub Before...

Call module1.mymodule

end sub

for instance.

HTH,
Jay