Saving a copy without incrementing number

[Pammi J]

hi
can someone help me - I have an invoice template which when opens increments
the invoice number in cell H16 by 1 each time.
Problem im having is that when i save as (to save a copy) when i reopen the
copy to veiw it - it carries on adding 1 to the invoice number.
Basically i only want the template to carry the incrementing number - not
the copy that i save from the template.
Could someone explain what it is i have to put in to stop it, and would this
be put into the workbook or a module - or in the same code as the
incrementing number?
Help would be appriciated Thankyou

 

[Bob I]

What is actually in Cell H16, a formula? Maybe try "copy, paste special,
value"

 

[Pammi J]

Hi bob
in the cell H16 i put the initial starting invoice number 100, and in the
workbook code i put the following;

Private Sub Workbook_Open()
Sheet1.Range("H16").Value = Sheet1.Range("H16").Value + 1
End Sub

it works great on adding one to the invoice number each time i open the
template, but when i want to save a copy (save as) its saves as (example -
invoice number 101) but if i need to veiw that copy - when i open it i get
the `enable/disable macro`s box pop up` and it then adds one to the invoice
number - it shouldnt change on the saved copy. How do i put that right?

Pammi J

 

[Bob I]

Delete/disable the macro.

Hi bob
in the cell H16 i put the initial starting invoice number 100, and in the
workbook code i put the following;

Private Sub Workbook_Open()
Sheet1.Range("H16").Value = Sheet1.Range("H16").Value + 1
End Sub

it works great on adding one to the invoice number each time i open the
template, but when i want to save a copy (save as) its saves as (example -
invoice number 101) but if i need to veiw that copy - when i open it i get
the `enable/disable macro`s box pop up` and it then adds one to the invoice
number - it shouldnt change on the saved copy. How do i put that right?

Pammi J

:

 

[Pammi J]

Bob
Is there a way of doing it without clicking the disable/enable macro popup
on opening. ie
fill out template with details
save as
then the copy would go away without carrying the template macro with it?

Then
when copy is opened for veiwing etc by other people - it would open exactly
as it was saved - with the same invoice number it was given when it was
filled in.

Pam

 

[Gord Dibben]

Chip Pearson shows how to delete VBA modules and/or routines from a workbook
using code.

http://www.cpearson.com/excel/vbe.aspx

Perhaps you can work out a way to delete the code when the workbook is first
saved.


Gord Dibben MS Excel MVP

 

[Pammi J]

Hi Gord
I tried the code to delete the code i have in to increment the invoice
number by 1, but it just stops the whole template from working.
I think there is a code to pick up a incrementing number from a txt file
that i can make and save in the same folder as i save the invoice template.
Maybe that code would be a quicker better option for me?
Im a beginner.

Pam

 

[Gord Dibben]

I'm not sure how to get around H16 updating without removing the code or
preventing it from running.

Hang in. I'll bet that one of the more VBA conversant persons will come up with
somrthing for you.


Gord

 

[Dave Peterson]

Private Sub Workbook_Open()
if thisworkbook.path = "" then
'it's never been saved, so increment
Sheet1.Range("H16").Value = Sheet1.Range("H16").Value + 1
end if
End Sub

 

[Gord Dibben]

Absatively brilliant but so simple when seeing it in person<g>

You my man!

Gord

 

[Dave Peterson]

Yeah, you say that until you see one of my screwups--then I can hear the
laughter!