Numerical Order Code for Macro

[cc11]

I am trying to find a code or fix what i have on the macro that will automatically make the certain column generate in numerical order when I press the new button. So basically I have a macro created but everytime i generate a new line(row) with the new button assigned to the macro in that specific column it keeps putting the number 0001 in and i am wanting it to go in ascending/numerical order (1,2,3,4,5, etc.) That probably isnt going to make sense, let me know if I need to explain better...

Here is the macro that I have created/recorded:
Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)
Sheets("PO Log").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[1]"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B9"), Type:=xlFillDefault
Range("B8:B9").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[-3]C[3]:R[-3]C[5]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[2]C[3]:R[2]C[4]"
Range("G9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[8]C[-4]"
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = "=SUM('PO (1)'!R[12]C[1]:R[39]C[1])"
Range("I9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[41]C"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[40]C[-1]"
Range("K9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[42]C[-2]:R[43]C[-2]"
Range("H21").Select
End Sub


The column/Row this is affected is B8 and B9. B8 is 0000 and B9 is 0001. Then when i press new the next row that is generated is 0001 and repeats each time.
Please someone help, I need to get this fixed ASAP!!!!

Thanks!

 

[cc11]

Needing to basically have a code to the column B8 to B308 and have it increase by 1 each time I generate a new row with the macro button

 

[cc11]

If anyone can answer this question it would be greatly appreciated I need it ASAP.

 

[Joe4]

I think it might make more sense if we could see the data (in addition to the code you already posted).
Please provide a sample of what your data looks like, and what you would to happen, based on that sample data.
Just be sure to remove/dummy-up any sensitive data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[cc11]

Due to this being my work computer I did not have access to download the Add-In. I took screen shots of what I am trying to do.


The POLOG image is where you will see the tab labeled sequential number. I am needing it to generate the next number each time I click the new purchase order button. I am wanting it to go from 1to300. So I will click the NEW PURCHASE ORDER Button that I have this macro listed below assigned to and it will generate everything down and then in the Sequential number tab it will generate the next number so it will go 1, 2, 3, 4, 5 etc. to 300.


The MACRO image is where you will see the codes I have listed. So I am wanting to find a code to do what I mentioned above to add into that Module.
Here is my all of my coding from the picture:

Sub NEWPURCHASEORDER()
'
' NEWPURCHASEORDER Macro
' CREATE NEW PURCHASE ORDER
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)
Sheets("PO Log").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[1]"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[-3]C[3]:R[-3]C[5]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[2]C[3]:R[2]C[4]"
Range("G9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[8]C[-4]"
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = "=SUM('PO (1)'!R[12]C[1]:R[39]C[1])"
Range("I9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[41]C"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[40]C[-1]"
Range("K9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[42]C[-2]:R[43]C[-2]"
Range("H21").Select
End Sub




Let me know if there is anything I could do or answer to get this figured out! Thank you so much!

 

[Joe4]

So, if you want "0001" in B9, "0002" in B10, etc, you can use formulas like you are now for the other cells.
This would be the initial formula in cell B9 (or B8):

=TEXT(ROW()-8,"0000")

The formula will automatically increment when copied down.

Also note that you can make your code more efficient by getting rid of all the selects/activecells that the Macro Recorder uses.
Line couplets like this:

Range("A9").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[1]"

can be simpliifed like this:

Range("A9").FormulaR1C1 = "=R[-7]C[1]"

 

[cc11]

so I put that in module 1 and it is saying
"Complie error:
Expected: line number or label or statement or end of statement"

Also with this code it would populate with the new row when I click the new purchase order button?

 

[Joe4]

OK, I thought you might be looking for dynamic code to end to the bottom of the list.
One question though. Does th following part need to be repeated every time a new row is added, or is that a one-time thing:

Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)

What is going on there?

 

[cc11]

No I have it set up right now so that everytime I click the add new purchase order button a new row is populated down from the previous one however i cannot get the sequential number column/tab to populate in numerical order each time I click the button.

As for the code I believe it needs to be repeated each time? I am not really sure I am new to the whole macro world! lol.

 

[Joe4]

OK, I am not sure what you are doing with that section of code, as you have not posted sheet "PO (0)".

But here is dynamic code to insert that row with formulas:

Sub NEWPURCHASEORDER()
'
' NEWPURCHASEORDER Macro
' CREATE NEW PURCHASE ORDER
'
' Keyboard Shortcut: Ctrl+Shift+H
'

    Dim lr As Long
    
'   ***NOT SURE WHAT YOU ARE DOING HERE***
    Sheets("PO (0)").Select
    Sheets("PO (0)").Copy After:=Sheets(3)
'   *************************************************************
    
    Sheets("PO Log").Select
    
'   Find last row with data in column A (Totals row)
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Insert new row
    Rows(lr).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'   Insert formulas
    Range("A" & lr).FormulaR1C1 = "=R2C2"
    Range("B" & lr).FormulaR1C1 = "=TEXT(ROW()-8,""0000"")"
    Range("C" & lr).FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
    Range("D" & lr).FormulaR1C1 = "='PO (1)'!R[-3]C[3]:R[-3]C[5]"
    Range("E" & lr).FormulaR1C1 = "='PO (1)'!R[2]C[3]:R[2]C[4]"
    Range("G" & lr).FormulaR1C1 = "='PO (1)'!R[8]C[-4]"
    Range("H" & lr).FormulaR1C1 = "=SUM('PO (1)'!R[12]C[1]:R[39]C[1])"
    Range("I" & lr).FormulaR1C1 = "='PO (1)'!R[41]C"
    Range("J" & lr).FormulaR1C1 = "='PO (1)'!R[40]C[-1]"
    Range("K" & lr).FormulaR1C1 = "='PO (1)'!R[42]C[-2]:R[43]C[-2]"

End Sub

Note that you may have to edit the R1C1 range references on your formulas referencing your "PO (1)" sheet.
It looks like you recorded the formulas using relative range references, which allows the row/column references to float, instead of locking them down with absolute range references.
If the ranges should be "locked", then you can re-record those formulas using the absolute range references, and replace the formula part of each one (like I did for your column A formula).

See here for a better understanding of the different kinds of range references:

Switch between relative, absolute, and mixed references - Microsoft Support

Use absolute or relative cell references in formulas, or a mix of both.

support.microsoft.com

Here is an explanation of how the R1C1 references work in VBA (though you do not really need to know that if you wrote the formulas correctly, inserting $ where necessary and recorded that, as Excel will figure that part out for you): R1C1-Style Notation And FormulaR1C1 Property In Excel VBA