macro to a number + 1 to a cell
 

i have a quote sheet and in h12 there is the quote number. i want change the quote number just before i close and save.when i open it again the next number will appear and i don't have to try to remember that quote number as in 2032 i hit a micro and the number goes to 2033 and saves

please help

macro to a number + 1 to a cell
 

Hi Alan
A small macro would do it.
Change the name of the sheet ("Quote") to yours
In the VBA editor under Project select "This workbook" and place the macro on
that sheet..
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Quote").Range("H12").Value = Sheets("Quote").Range("H12").Value + 1
ThisWorkbook.Save
End Sub
HTH
Cimjet


"Alan R" wrote in message
...
i have a quote sheet and in h12 there is the quote number. i want change the
quote number just before i close and save.when i open it again the next number
will appear and i don't have to try to remember that quote number as in 2032 i
hit a micro and the number goes to 2033 and saves

please help

macro to a number + 1 to a cell
 

I must be doing something wrong since i
Changed sheet name to qoute then i right
clicked on the word quote and opened vba
selected worksheet and pasted


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Quote").Range("H12").Value = Sheets("Quote").Range("H12").Value + 1
ThisWorkbook.Save
End Sub

then i hit save and closed the work sheet and the value stayed the same

macro to a number + 1 to a cell
 

or is there a way to have the value change on close

macro to a number + 1 to a cell
 

"Alan R" wrote in message
...
or is there a way to have the value change on close


that question was answered, I believe, in a post by Gord earlier.

The macro needs to be posted into the "This Workbook" module not the
worksheet module ... so when you right click on the sheet tab and "view
Code", you need to then select the "this workbook" module before pasting
the sample code.

I believe there is a project manager pane open on the left side of the
screen by default that will show the different modules. If it is not
showing, check the options under the View menu.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

macro to a number + 1 to a cell
 

Alan
You right click on the sheet tab then select View Code, but before pasting the
script, you must select "This Workbook".
Don't paste in the Worksheet. In the VBA Project on the left side, select "This
Workbook" by double clicking on it.
The script will update your number before you close your workbook and it will
save it.
You may want to read Gord's comment before proceeding.
HTH
Cimjet
"Alan R" wrote in message
...
I must be doing something wrong since i
Changed sheet name to qoute then i right
clicked on the word quote and opened vba
selected worksheet and pasted


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Quote").Range("H12").Value = Sheets("Quote").Range("H12").Value + 1
ThisWorkbook.Save
End Sub

then i hit save and closed the work sheet and the value stayed the same

macro to a number + 1 to a cell
 

Cimjet's original post explained where to place the code......in
Thisworkbook module.

I simply added a caveat about using close event rather than save
event.


Gord

On Wed, 14 Sep 2011 18:34:58 -0500, "Clif McIrvin"
wrote:

"Alan R" wrote in message
...
or is there a way to have the value change on close


that question was answered, I believe, in a post by Gord earlier.

The macro needs to be posted into the "This Workbook" module not the
worksheet module ... so when you right click on the sheet tab and "view
Code", you need to then select the "this workbook" module before pasting
the sample code.

I believe there is a project manager pane open on the left side of the
screen by default that will show the different modules. If it is not
showing, check the options under the View menu.

macro to a number + 1 to a cell
 

Hi Gord
Actually it's a good warning and something for him to consider but sometime they
need to experience it for themselves

"Gord" wrote in message
...
Cimjet's original post explained where to place the code......in
Thisworkbook module.

I simply added a caveat about using close event rather than save
event.


Gord

On Wed, 14 Sep 2011 18:34:58 -0500, "Clif McIrvin"
wrote:

"Alan R" wrote in message
...
or is there a way to have the value change on close


that question was answered, I believe, in a post by Gord earlier.

The macro needs to be posted into the "This Workbook" module not the
worksheet module ... so when you right click on the sheet tab and "view
Code", you need to then select the "this workbook" module before pasting
the sample code.

I believe there is a project manager pane open on the left side of the
screen by default that will show the different modules. If it is not
showing, check the options under the View menu.

macro to a number + 1 to a cell
 

Thank you all for your assistance in helping me with the proper macro on my excel sheet. After reading everyones response and using all your advices I was able to get the sheet the way I wanted it.

macro to a number + 1 to a cell
 

You're welcome
Thanks for the feedback
"Alan R" wrote in message
...
Thank you all for your assistance in helping me with the proper macro on my
excel sheet. After reading everyones response and using all your advices I
was able to get the sheet the way I wanted it.