macro to a number + 1 to a cell

  • Thread starter Thread starter Alan R
  • Start date Start date
A

Alan R

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
 
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


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
 
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
 
Alan R said:
or is there a way to have the value change on close
Click to expand...


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.
 
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
 
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
 
Hi Gord
Actually it's a good warning and something for him to consider but sometime they
need to experience it for themselves
 
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.
 
Back
Top