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
Bookmarks