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]

I did that and pressed the button, for the first one it said ATTN: and for the second, third, and fourth and so on it left it blank, in cell h2.

 

[Joe4]

Can you go to the "PO (1)" sheet, and post the formula that the code is putting in cell H2?

 

[cc11]

Can you go to the "PO (1)" sheet, and post the formula that the code is putting in cell H2?

='PO Log'!C8

 

[Joe4]

We have made a bunch of changes to the original code.
Can you please post the code you currently have now, in its entirety, so we can be sure that we are both working off of the same code?

 

[cc11]

We have made a bunch of changes to the original code.
Can you please post the code you currently have now, in its entirety, so we can be sure that we are both working off of the same code?

yes, here it is:

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

Dim lr As Long
Dim ws As String

' Create new sheet
Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)

' Get name of new sheet
ws = ActiveSheet.Name

' Find last row with data in column A (Totals row)
Sheets("PO Log").Select
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 = "='" & ws & "'!R6C7"
Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R11C8"
Range("G" & lr).FormulaR1C1 = "='" & ws & "'!R[8]C[-4]"
Range("H" & lr).FormulaR1C1 = "=SUM('" & ws & "'!R[12]C[1]:R[39]C[1])"
Range("I" & lr).FormulaR1C1 = "='" & ws & "'!R[41]C"
Range("J" & lr).FormulaR1C1 = "='" & ws & "'!R[40]C[-1]"
Range("K" & lr).FormulaR1C1 = "='" & ws & "'!R51C9"

End Sub

 

[Joe4]

I think we had it pulling from the wrong sheet.
See if this works for you:

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

    Dim lr As Long
    Dim ws As String

'   Create new sheet
    Sheets("PO (0)").Select
    Sheets("PO (0)").Copy After:=Sheets(3)

'   Get name of new sheet
    ws = ActiveSheet.Name

'   Find last row with data in column A (Totals row)
    Sheets("PO Log").Select
    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 = "='" & ws & "'!R6C7"
    Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R11C8"
    Range("G" & lr).FormulaR1C1 = "='" & ws & "'!R[8]C[-4]"
    Range("H" & lr).FormulaR1C1 = "=SUM('" & ws & "'!R[12]C[1]:R[39]C[1])"
    Range("I" & lr).FormulaR1C1 = "='" & ws & "'!R[41]C"
    Range("J" & lr).FormulaR1C1 = "='" & ws & "'!R[40]C[-1]"
    Range("K" & lr).FormulaR1C1 = "='" & ws & "'!R51C9"
    
'   Populate cell H2 on new sheet with formula to pull from column C
    Sheets(ws).Range("H2").Formula = "='PO Log'!C" & lr

End Sub

Also for future use, when posting code, plus use the "Code" tags. This maintain formatting and spacing, and make the code easier to view and work with.
There are instructions here on how to do that: How to Post Your VBA Code

 

[cc11]

That works perfect, exactly what I needed. Thanks again for all your help!!!!!

And I will start using the code tags, my apologies.

 

[Joe4]

You are welcome.