Automatic PO numbering help

[Pumpkinjack]

Hi Everybody:

First, what a great site, it has helped me a lot in the past, THANK YOU MR. EXCEL!
Here is my quandry. I need to set up an internal PO template for my department. I would like to have a cell generate a PO number automatically upon opening the workbook. Preferably, I would like the number to consist of the current date and a number that will sequentially change each time I open up a new PO. Example:

Today's date is 5/21/02
I would like to have a PO Number generate automatically as 52102-1
Then, after I save the PO, the PO number cell would automatically change to 52102-2
and so on.

Is there such a formula? I can usually get around with basic formulas requests but I cannot figure this out for the life of me. If anyone has any ideas, please post.

Thanks!

[Ian Mac]

On 2002-05-21 07:56, Pumpkinjack wrote:
Hi Everybody:

First, what a great site, it has helped me a lot in the past, THANK YOU MR. EXCEL!
Here is my quandry. I need to set up an internal PO template for my department. I would like to have a cell generate a PO number automatically upon opening the workbook. Preferably, I would like the number to consist of the current date and a number that will sequentially change each time I open up a new PO. Example:

Today's date is 5/21/02
I would like to have a PO Number generate automatically as 52102-1
Then, after I save the PO, the PO number cell would automatically change to 52102-2
and so on.

Is there such a formula? I can usually get around with basic formulas requests but I cannot figure this out for the life of me. If anyone has any ideas, please post.

Thanks!

There isn't such a formula. You need to use VBA. If your comfortable trying it........

Goto View>Toolbars>Visual Basic

Click on the Visual Basic Editor button Next to the Hammer and Spanner Button.

With the Editor open find a something titled

VBAProject(TheNameOfYourFile)

Under that Double Click on the ThisWorkbook icon.

Note: If this is not visible click on the + signs until you find it.

Now you should have:

Private Sub Workbook_Open()

End Sub

Copy the following within the 2 lines.

----------------------------------------

If Left(Sheet1.Range("a1").Value, 6) = Format(Now(), "mmddyy") Then

Sheet1.Range("a1").Value = Left(Sheet1.Range("a1").Value, 6) & "-" & Format(Right(Sheet1.Range("a1").Value, 2) + 1, "00")

Else

Sheet1.Range("a1").Value = Format(Now(), "mmddyy") & "-01"

End If

----------------------------------------

you should now have:


Private Sub Workbook_Open()

If Left(Sheet1.Range("a1").Value, 6) = Format(Now(), "mmddyy") Then

Sheet1.Range("a1").Value = Left(Sheet1.Range("a1").Value, 6) & "-" & Format(Right(Sheet1.Range("a1").Value, 2) + 1, "00")

Else

Sheet1.Range("a1").Value = Format(Now(), "mmddyy") & "-01"

End If

End Sub


This should be what you need, by the way I changed the 52102 to 052102 which made it easier for me to write.

Also change the "A1" to which ever you need, and you can change the Sheet1 also baring in mind that Sheet1 is the name that Excel gives the sheet NOT you.

If you have any problems post back.

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-05-21 08:26 ]

[Pumpkinjack]

Thanks Ian!

Yes that worked but there's a small problem. When I close out of a PO that is finished, and then go back and reopen the same PO, the PO number changes itself.

Example:
I create PO number 052102-01. Save the PO.
Close out of Excel.
Reopen the completed PO #052102-01. The PO number changes automatically again to 052102-02.

Again, the solution you sent is a godsend, thank you so much Ian! But now I this new unplanned problem. Any other ideas. Or is it better to disable the macros whenever I open up a finished PO.

I hope I did not come across as ungrateful, that was not my intention at all.

Peter

[Ian Mac]

On 2002-05-21 08:50, Pumpkinjack wrote:
Thanks Ian!

Yes that worked but there's a small problem. When I close out of a PO that is finished, and then go back and reopen the same PO, the PO number changes itself.

Example:
I create PO number 052102-01. Save the PO.
Close out of Excel.
Reopen the completed PO #052102-01. The PO number changes automatically again to 052102-02.

Again, the solution you sent is a godsend, thank you so much Ian! But now I this new unplanned problem. Any other ideas. Or is it better to disable the macros whenever I open up a finished PO.

I hope I did not come across as ungrateful, that was not my intention at all.

Peter

not ungreatful at all, from small acorns and all that.

I'm assuming your changing the name once any work has been done.
What about something like:

Private Sub Workbook_Open()

If ThisWorkbook.Name = "PO Number.xls" Then

If Left(Sheet1.Range("a1").Value, 6) = Format(Now(), "mmddyy") Then

Sheet1.Range("a1").Value = Left(Sheet1.Range("a1").Value, 6) & "-" & Format(Right(Sheet1.Range("a1").Value, 2) + 1, "00")

Else

Sheet1.Range("a1").Value = Format(Now(), "mmddyy") & "-01"

End If

Else

End If

End Sub

ALSO you could get the workbook to save automatically, with:

Private Sub Workbook_Open()

If ThisWorkbook.Name = "PO Number.xls" Then

If Left(Sheet1.Range("a1").Value, 6) = Format(Now(), "mmddyy") Then

Sheet1.Range("a1").Value = Left(Sheet1.Range("a1").Value, 6) & "-" & Format(Right(Sheet1.Range("a1").Value, 2) + 1, "00")

Else

Sheet1.Range("a1").Value = Format(Now(), "mmddyy") & "-01"

End If

ThisWorkbook.SaveAs "C:PO Number_" & Sheet1.Range("A1").Value

Else

End If

End Sub

Change the location as required.

[Pumpkinjack]

Ian you are such a life saver!!

I like the idea of the automatic save. When I put in the "VBA stuff", save it then go back to open the file, I get the following:

Run Time error '1004':

The file could not be accessed

Then I hit debug button and the following line gets highlighted:

ThisWorkbook.SaveAs "C:\PO Number_" & Sheet1.Range("a1").Value

Did I type something wrong? I apologize for being so dense.

Also, if I was to change all the "A1" entries to say "B2", would the result appear in the B2 cell?

Thank you again Ian!

Best regards,
Peter the Excel Impaired

[Ian Mac]

On 2002-05-21 09:36, Pumpkinjack wrote:
Ian you are such a life saver!!

I like the idea of the automatic save. When I put in the "VBA stuff", save it then go back to open the file, I get the following:

Run Time error '1004':

The file could not be accessed

Then I hit debug button and the following line gets highlighted:

ThisWorkbook.SaveAs "C:\PO Number_" & Sheet1.Range("a1").Value

Did I type something wrong? I apologize for being so dense.

Also, if I was to change all the "A1" entries to say "B2", would the result appear in the B2 cell?

Thank you again Ian!

Best regards,
Peter the Excel Impaired

Yes you can change A1 to B1

as for the other, it appears that if you put a backslash on the board you get 2.

You need to put

ThisWorkbook.SaveAs "C:ONE backslash herePO Number_" & Sheet1.Range("a1").Value

You can also change the path (remember for each double backslash, one)

ThisWorkbook.SaveAs "C:first folder namesecond folder namePO Number_" & Sheet1.Range("a1").Value

[Pumpkinjack]

I said it before and I'll say it again, Ian you are a life saver!!

Thank you so much for your help!!

Best regards,
Pete

[louis reichert]

I am trying to do the same peter did but I cannot get it to work please help