Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Slight Problem with Private Sub Workbook_BeforeSave Macro

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •