Slight Problem with Private Sub Workbook_BeforeSave Macro

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top