Results 1 to 7 of 7

Macro operates on 'closed' workbook & Current Active WB

Threaded View

  1. #1
    Registered User
    Join Date
    03-17-2011
    Location
    Guelph, Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    63

    Angry Macro operates on 'closed' workbook & Current Active WB

    Situation:
    Contract Template is opened, auto numbering macro is run.
    File is 'Saved As' with new name..
    SAVED Template is re-opened and used as a 'template' for new Contract
    (since changes required are minor).
    The Template is then "Saved As' with a new name
    The numbering macro is run (It increments as it should)
    Then SAVED again.

    Here comes the problem.
    On MOST occasions, if the original is now re-opened, the number has incremented in it as well.
    Also, my testing confirms that the Salesmans Name and Managers Name portion of the macro is also 'redone'.

    The only thing that I can come up with is that somehow the 'Set myBook' does not 'release' (for want of a better word).

    This problem apparently been in existence since I created the macro, but I was just made aware of it.

    If an experienced eye could review my code and let me know what the problem code is and/or how to circumvent this problem, it would be greatly appreciated.

    
    Sub QuoteNum()
    '   September 29, 2005
    '   Creates and Inserts Sequential Quotation Number on CoverPage of Contract
    
            Dim myBook As Workbook
                    '( The current selected Contract Template)
            Dim myQCNUM As Workbook
                    '(Provides Quotation #, Salesman & Managers Name for Contract)
    
            Application.ScreenUpdating = False
            Application.EnableEvents = False
    
    '   The "CoverPage" to be the active WS when initiating macro
            Set myBook = ActiveWorkbook
                Sheets("CoverPage").Activate
    '                ActiveSheet.Unprotect Password:="xxxxx"
    
            Set myQCNUM = Workbooks.Open("C:\Excel Add_Ins\QCNUM.xls")
    
    '   QCNUM is now Open (available)
    '   Go to QCNUM, Sheet #2, Reference cell G6
    '   (this is a concatenated number of 3 different cells)
    '    Go to myBook, Sheet "CoverPage", Reference cell E4
    
           myBook.Worksheets("CoverPage").Range("E4").Value = _
           myQCNUM.Worksheets("Sheet2").Range("G6").Value
    
    '   Now go to QCNUM, Sheet # 1, B6 and get Salesmans Name
    '   Go to myBook, Sheet "CoverPage", Reference cell C38
    
            myBook.Worksheets("CoverPage").Range("C38").Value = _
            myQCNUM.Worksheets("Sheet1").Range("B6").Value
    
    '   Go to QCNUM, Sheet # 1, F6 and Copy Managers Name
    '   Go to myBook, Sheet "CoverPage", Reference cell E38
    
            myBook.Worksheets("CoverPage").Range("E38").Value = _
            myQCNUM.Worksheets("Sheet1").Range("F6").Value
    
    '       myBook.ActiveSheet.Protect Password:="xxxxx"
    
    '  The following increments the number in QCNUM.XLS for use on next Contract
        '  QCNUM.xls:  Copy Cell G4,  Paste to Cell G3
        '  This copies the most recent used number (G4)into the "starting
        '  number"  cell (G3), which in turn makes increments (G4), by 1
    
            With myQCNUM.Worksheets("Sheet2")
                .Range("G3").Value = .Range("G4").Value
            End With
    
    ' Save only myBook
            With myBook
                .Save
            End With
    
     'Save and Close QCNUM.xls
            With myQCNUM
                .Save
                .Close Savechanges:=True
            End With
    
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    
    End Sub
    Attached Files Attached Files
    Last edited by BEEJAY; 05-03-2011 at 03:32 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1