Auto incrementing PO number

[rackham]

Hi all. I'm new here, so I'm sorry if there have been a ton of these questions asked already. I searched a bit, but am still having difficulty with my problem. I have been trying many different VB codes to help my situation, but as of yet, haven't gotten it sorted. I'm very new to VB as well, so I don't understand a lot of things with coding, things need to spelled out a bit for me.
Ok, so here's my problem - I have a Purchase order template called Purchase Order form in Excel 2003. My actual spreadsheet I've titled Sales Order. In the cell G4 I have my purchase order number, which I would like to have auto increment for NEW purchase orders only, not the ones I've already done and saved as a new file name.
The problem I've encountered with the VB codes I've used thus far is that it does everything I want it to do the first time, but then after that the new purchase orders do not auto increment, or it auto increments on all my purchase orders, both new and previously saved ones. Getting really frustrated with this because it seems that other people are able to make the VB code work, but I'm not. As I've said, I'm not a VB genius, so I need everything spelled out please. I hope someone here can help. I've not had success on another forum.

[royUK]

I would think that there is a problem with the design of your workbook.

Attach an example

[rackham]

Here is my spreadsheet...at least I hope it's attached

[royUK]

I can't follow your code with the VB Editor.

Have a look at the amended workbook

[rackham]

Ok, I've looked at the spreadsheet and the code....it's one of the codes I've used previously, but I've never seen the save button on the spreadsheet. Sorry to be thick, but what do I do with it? Do I click on the save button if I want to save a purchase order, or do I still go through save as? I just want to try it out several times before I get excited

[rackham]

I've tried saving the spreadsheet and the PO number changes the second I click on "save as"..is that supposed to happen? Also, the template number is still the original number, everytime I open it, it is showing as 7023, never changes when I open it?

[royUK]

I've re-checked the code & made sure that it is assigned to the button

[rackham]

I'm really sorry, I'm obviously doing something wrong because it's still incrementing the PO number when I click on "save as"? Could you give me a step by step to see what I'm doing wrong? I notice in your VB code it says "saveas" in the first line...is that why it's incrementing upon clicking the "save as" button? Can I have it so it increments upon closing or something? I'm really sorry for being so difficult, but I really don't get VB at all.

[royUK]

The code increments the number then saves a copy of the worksheet as a separate workbook. All you need to do is click the save button, no save as.

If you want to save then increment just move the code around like this

Sub Macro1()
    'save a copy to new workbook
    ActiveSheet.Copy
    'rename new workbook
    ActiveWorkbook.SaveAs "Purchase Order " & Range("PoNum").Value & ".xls"
    ActiveWorkbook.Close True 'close & save new workbook
    'advance number
    Range("PoNum").Value = Range("PoNum").Value + 1
    ThisWorkbook.Save 'save the master workbook changes
End Sub

[rackham]

so do I put that code into the VB thisworkbook spot, same as I have been or is it a macro..I just notice it says submacro and am a bit confused? sorry again. getting really embarrassed now

[royUK]

It's actually in a macro that you added & attached to the button. I edited the code

Add a button


Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[rackham]

I've done the run macro thing and it's come up with an error message straight away saying: Run time error '9' Subscript out of range? What does that mean. It gives me the option to debug. But problems in the past that I've tried the debug has not worked, so again, don't know if I'm doing it right?

[rackham]

Also, I notice that you told me to put the code in the module, whereas in the past I've always been putting the code into the ThisWorkbook part. What is the difference between the two?

[royUK]

I've made all the amendments assuming that was the workbook you would use. You can see where I've put the code & match it in the other workbook.

I have added a Named Range for the PO number, which needs adding to your workbook

Generally you don't add macros to the workbook module, but add a module for them.

Debugging will highlight the problems, you need to then find what the problem is.

[rackham]

ok I've tried again and I've gotten another error message, when I go to debug it's highlighted this part of the code:

ActiveWorkbook.SaveAs "Purchase Order " & Range("PoNum").Value & ".xls"

(I hope I did the code tag properly...first time I've had to do it)

what does that part of the error mean?

Also, you've said

have added a Named Range for the PO number, which needs adding to your workbook

what does that mean?

[rackham]

ok, I think I named the cell I want the PO number to be in. I went to insert - name - define - and it came up automatically with Purchase_Order_Number, so I clicked the add button and then I got the same error message as before. So I've obviously done something wrong...what else can I do please? Really sorry to be such a pain, but I really want to understand this, I just don't.

[royUK]

Named Ranges are basic Excel features

because you haven't crated the named range

[royUK]

You have used a differnt name than the code, so the code needs changing

Sub Macro1()
    'save a copy to new workbook
    ActiveSheet.Copy
    'rename new workbook
    ActiveWorkbook.SaveAs "Purchase Order " & Range("Purchase_Order_Number").Value & ".xls"
    ActiveWorkbook.Close True 'close & save new workbook
    'advance number
    Range("PoNum").Value = Range("Purchase_Order_Number").Value + 1
    ThisWorkbook.Save 'save the master workbook changes
End Sub

[rackham]

I have changed both of the parts in the code to show Purchase_Order_Number, but it still had an error message come up showing this line as wrong

Range("PoNum").Value = Range("Purchase_Order_Number").Value + 1

I then changed the first "PoNum" also to "Purchase_Order_Number", but that still showed as wrong. Still really not sure why it's not working for me, but it works for everyone else. I'm following the directions given to me and it still doesn't work...user error I know, but I really don't get what I'm doing wrong? Maybe I should just give up on this idea of having the number auto increment...if it's this difficult and not working. I don't know that I should waste anymore time trying to do it.

[royUK]

That line still has the old name in it

[rackham]

I have changed it to this:

Range("Purchase_Order_Number").Value = Range("Purchase_Order_Number").Value + 1

and it still doesn't work, I still get the error message and when I go into debug, that line still shows up as highlighted

[royUK]

Attach the actual workbook that you are working with

[rackham]

I see what's happening now! I think I had old VB code in the "ThisWorkbook" section first of all. Second, I didn't realise that to increment the number on the spreadsheet I needed to run the macro after each time I saved a spreadsheet under a different name. I think it's finally working for me now. Thank you so much. I think the light finally came on above my head you're so thanks again...especially for being patient with me. Will definitely come back here for all my excel questions in the future.

[royUK]

If you use my code in the master workbook & save copies then you just click the button & keep re-using the original workbook