i want to set up automatic next number generating for a purchase order spread
sheet

In principle it's simple but it would help if your provided more detail.

Specifically, where should Excel look for the last number. Is it in the open
or another workbook or a file.

"huwzee" wrote:

i want to set up automatic next number generating for a purchase order spread
sheet

Hi Mike

i need it to generate the next number in the sequence i.e. 001, 002, 003,
etc for each individual purchase order, it should look for the number from
the previous purchase order, i just want 1 specific cell on the spreadsheet
to do it.

thanks,
steve

"Mike" wrote:

In principle it's simple but it would help if your provided more detail.

Specifically, where should Excel look for the last number. Is it in the open
or another workbook or a file.

"huwzee" wrote:

i want to set up automatic next number generating for a purchase order spread
sheet

Ok, One approach.

paste this macro in the current PO workbook you are using

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)ponum = link to the cell where the PO number is stored
Workbooks.Add
Filename = "NextPOnumber" & ponum + 1
ActiveWorkbook.SaveAs Filename:=Filename
End Sub

Somewhere in the work I imagine you will have the PO number. Edit the line
ponum to point to the cell that contains this number.

The macro will run when you try to save your current bok and it will open a
new book and save it with the file name nextponumber# where # is one more
than the current book.

Was this helpful?
"huwzee" wrote:

i want to set up automatic next number generating for a purchase order spread
sheet

hi mike
ive set up the macro how do i link it to a specific cell

"Mike" wrote:

Ok, One approach.

paste this macro in the current PO workbook you are using

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)ponum = link to the cell where the PO number is stored
Workbooks.Add
Filename = "NextPOnumber" & ponum + 1
ActiveWorkbook.SaveAs Filename:=Filename
End Sub

Somewhere in the work I imagine you will have the PO number. Edit the line
ponum to point to the cell that contains this number.

The macro will run when you try to save your current bok and it will open a
new book and save it with the file name nextponumber# where # is one more
than the current book.

Was this helpful?
"huwzee" wrote:

i want to set up automatic next number generating for a purchase order spread
sheet

ponum = link to the cell where the PO number is stored

ponum= worksheets("Sheet1").cells(1,1).value

adjust to suit

"huwzee" wrote:

hi mike
ive set up the macro how do i link it to a specific cell

"Mike" wrote:

Ok, One approach.

paste this macro in the current PO workbook you are using

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)ponum = link to the cell where the PO number is stored
Workbooks.Add
Filename = "NextPOnumber" & ponum + 1
ActiveWorkbook.SaveAs Filename:=Filename
End Sub

Somewhere in the work I imagine you will have the PO number. Edit the line
ponum to point to the cell that contains this number.

The macro will run when you try to save your current bok and it will open a
new book and save it with the file name nextponumber# where # is one more
than the current book.

Was this helpful?
"huwzee" wrote:

i want to set up automatic next number generating for a purchase order spread
sheet