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]

Sorry I have one more question to ask...

So on the PO Log column C8 it pulls from column A8 and B8 to get its number and so on and it works correctly however it is not displayed on the PO tab for whatever number it is. Which is supposed to be on Column H2.

 

[Joe4]

You are welcome.
Glad we were able to work through all the issues!

 

[cc11]

Sorry I have one more question to ask...

So on the PO Log column C8 it pulls from column A8 and B8 to get its number and so on and it works correctly however it is not displayed on the PO tab for whatever number it is. Which is supposed to be on Column H2.

Are you still able to answer this question?

 

[Joe4]

Are you still able to answer this question?

Can you show me an example of that that looks like?

 

[cc11]

Can you show me an example of that that looks like?

So on the PO LOG(refer to po log pic) when i generate a newpurchaseorder via the button the PO Number column C generates in number order just fine and is being populated off of column A & B combined.

However that number that is populated in the C column is supposed to then be reflected in the PO (refer to po pic) on H2 under purchase order and it keeps copying 00000.
I need it to then copy over for each row i generate with the button so it would be 00001 for PO1 and so on.

 

[Joe4]

Cell H2 is not being referenced anywhere in the VBA code. So I don't think that was ever addressed in anything we have talked about or done here so far.
It seems to be a new issue altogether.

Is there a formula in cell H2? If so, what is it?
Is it just a hard-coded number that is being copied from the original sheet?

 

[cc11]

This is the code for cell H2 ='PO Log'!C8 and it is reflected from the po log in cell c8

C8 on the po log is combined from a8 and b8 the formula in c8 is =CONCATENATE(A8,B8)

i am wanting it to then reflect down when i hit the newpurchaseorder button so for PO(0) in the po log on cell c8 the number is 00000 and on PO(0) tab in cell h2 it says 00000 which is reflected from cell c8
and then i click the newpurchaseorder button and it gives me PO(1) in the po log on cell c8 it says 00001 and on the PO(1) tab in cell h2 it says 00000 but i am wanting it to copy from c8 and it should then be 00001 and so on.

 

[Joe4]

and then i click the newpurchaseorder button and it gives me PO(1) in the po log on cell c8 it says 00001 and on the PO(1) tab in cell h2 it says 00000 but i am wanting it to copy from c8 and it should then be 00001 and so on.

When you click on the button, shouldn't it then pull from cell C9, where the 00001 exists, and not from C8 (as new records are added, it adds new rows, so that latest record keeps moving down one row, right)?

And is shouldn't it all be in sync, so that the 00001 appears on sheet "PO(1)", and 00002 appears on sheet "PO(2)", etc?

 

[cc11]

When you click on the button, shouldn't it then pull from cell C9, where the 00001 exists, and not from C8 (as new records are added, it adds new rows, so that latest record keeps moving down one row, right)?

And is shouldn't it all be in sync, so that the 00001 appears on sheet "PO(1)", and 00002 appears on sheet "PO(2)", etc?

correct everything is in sync and it pulls into the PO Log correctly so i click the newpurchaseorder button and it generate 00001 on the po log but when i click over to the tab PO(1) it still says 00000 and is pulling the info from the po log c8 instead of c9

 

[Joe4]

Try placing this just before the "End Sub" line at the end of your code, and see if it does what you want:

'   Populate cell H2 on new sheet with formula to pull from column C
    Sheets(ws).Range("H2").Formula = "='PO (0)'!C" & lr