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]

Thank you I will try this and let you know if it works!

 

[cc11]

Thank you so much that worked perfectly.

 

[cc11]

I do still have an issue...

So whenever i click new purcahse order button it populates the next line and then it adds a tab. For PO 1 it pull the info from that tab. Whenever I do the second one it doesnt pull the information off the 2nd tab for that specific one and pulls the information off of the first one.

 

[Joe4]

OK, I think you then need to dynamically capture the name of the sheet you are creating at the beginning, and use that sheet name in your formulas, i.e.

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 & "'!R[-3]C[3]:R[-3]C[5]"
    Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R[2]C[3]:R[2]C[4]"
    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 & "'!R[42]C[-2]:R[43]C[-2]"

End Sub

 

[cc11]

That works great the only thing now that will not populate into the cell is the vendor and quote# columns on the PO Log from the PO(whatever number it is) tab(s).

for vendor it should be taking the formula from the PO(whatever number) tab which i have the formula for that as ='PO (1)'!G6:I6

and the same with the quote# and the formual i have is =@'PO (1)'!H11:I11

 

[cc11]

That works great the only thing now that will not populate into the cell is the vendor and quote# columns on the PO Log from the PO(whatever number it is) tab(s).

for vendor it should be taking the formula from the PO(whatever number) tab which i have the formula for that as ='PO (1)'!G6:I6

and the same with the quote# and the formual i have is =@'PO (1)'!H11:I11

as well as the total tab which the formula is =@'PO (1)'!I51:I52

 

[Joe4]

That works great the only thing now that will not populate into the cell is the vendor and quote# columns on the PO Log from the PO(whatever number it is) tab(s).

for vendor it should be taking the formula from the PO(whatever number) tab which i have the formula for that as ='PO (1)'!G6:I6

and the same with the quote# and the formual i have is =@'PO (1)'!H11:I11

I am sorry, but I am not following. Where exactly is this formula you are talking about located on your sheet?
Remember, while you have access to all the sheets and data, we don't. All that we have to go on is what you are showing us. And I am a very visual person.

 

[cc11]

Yes i understand that.

So on the PO Log tab where everything populates into and where the NEW PURCHASE ORDER BUTTON IS , is where I am talking about.

So on there is the chart where everything populates into from which ever PO tab you are working on for example PO #1 and on the vendor, quote #, and Total column ,they are all separate, ( if you look at the picture i posted you will be able to see it) they are not populating in the information from where i fill it out on the PO#1 tab or whatever number I am working on(using number 1 as the example).

but all the other columns are.

 

[Joe4]

So on there is the chart where everything populates into from which ever PO tab you are working on for example PO #1 and on the vendor, quote #, and Total column ,they are all separate, ( if you look at the picture i posted you will be able to see it) they are not populating in the information from where i fill it out on the PO#1 tab or whatever number I am working on(using number 1 as the example).

but all the other columns are.

Can you tell me the EXACT cell address of where this formula that is not being updated correctly resides?
Or show a picture?

 

[cc11]

okay so the pic labeled "po log" is where you will see the vendor, quote and total.

on the "po 1 front half" pic is where i fill in the info and it should automatically populate into the cells on the po log.

the po 1 bottom pic is where the total should come from.