Macro operates on 'closed' workbook & Current Active WB

[BEEJAY]

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.

Please Login or Register  to view this content.

[Wizz]

Concider the followings:

Add the following Code at the end of your procedure (To release te variable):

Please Login or Register  to view this content.

Consider using:

Please Login or Register  to view this content.

Very important analyze again your Saving/Opening order.

Your macro is saving the workbook, so if you Open the workbook; run the macro and then "save it As" with another name then you end up with two identical workbooks.

[royUK]

Where is the macro stored?

Baically I would have a template file (*.xlt). The code would open that & add the sequential number. In cases like this the master workbook would have the code & a summary of relevant information would be saved, i.e customer, number, sales person etc. In fact I generally don't have separate workbooks for individual quotes but use this summary to recreate quotes and/or convert to invoices based on the stored data.

If the code were in a an addin or separate workbook the number could be stored.

[BEEJAY]

Wizz:
I expect the first codes supplied will do the trick. Since the problem is intermitent on my computer, I will modify the code on my bosses computer and let him try it out.
The second code bogs down the macro when it tries to 'copy' from one sheet to another. When I go back to 'ActiveWorkbook', it works fine again.

Thanks so much for your input. I was not 'familiar' with = Nothing, and could not find anything in my manuals or on the .net about it.

[BEEJAY]

RoyUK
Reading thru your post several times - I get impression your quote process/procedure is substantially different from mine.
For your information:
16 Contract Templates, 17 Salesmen / Dealers. On everyones computer:
All Contract Templates are Stored in C:\Contract Templates
Custom Add-ins are Stored in C:\Excel Add_Ins
QCNUM.XLS: Provides Sequential number, and names
Custom Menu A: Provides quick retreival of required Contract Template
Custom Menu B: Provides quick access to select next quote #, and a host of printing options
The Contract Templates themself consist of:
Cover Page; Truck Specs Sheet; Options, Selectable Sheet; Unit Specs Sheet; Notes Sheets; Warranty Sheets;
The biggest template has Unit Specs sheet consisting of almost 2300 rows.
I'm rather proud of how these templates have evolved over the years. The Salesmen claim it saves them hours and hours in doing quotes. HOWEVER, I am away, and would the first to admit, that there probably is a better way to do this. In the meantime, I try to read as much as I can about Excel and programming, so I can improve the 'system'.

I greatly appreciate your thoughts and comments - Often ideas start to germinate from comments recieved, which, in time, turn into desireable upgrades to the system/process.

Thanks again.

[Wizz]

The second code bogs down the macro when it tries to 'copy' from one sheet to another. When I go back to 'ActiveWorkbook', it works fine again.

Sorry, I didnt check that your code ("QuoteNum") was in a different workbook than the Template workbook ("myBook").
'Thisworkbook' is very useful when both code and the referenced workbook are the same.

It is a good practice to allways release your object variables (Set myVariable = Nothing) and your arrays (erase myArray).

[BEEJAY]

Sorry, I didnt check that your code ("QuoteNum") was in a different workbook than the Template workbook ("myBook").
'Thisworkbook' is very useful when both code and the referenced workbook are the same.

That is helpful - Thank-you

It is a good practice to allways release your object variables (Set myVariable = Nothing) and your arrays (erase myArray).

This idea is new to me, yet so logical. I'll be reviewing all my macros (lots of them) for this situation. Thank-you