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]

also on the pic po log 1 front half where it says job number that is not populating and it should come from the pic po log where job number is.

also here is a better pic of the PO log where you can see column letters and row numbers.

 

[cc11]

and here is a better pic of the Po log 1 bottom half so you can see the rows and cloumns as well.

 

[Joe4]

OK, so it looks like you are talking about the formulas in columns D and E.
Did you adjust these formulas like I mentioned at the bottom of post 10 (the one that you marked as the solution)?
Your original formulas were relative references, and not absolute references, so as you copy formulas down the page, they will no longer line up.

 

[cc11]

OK, so it looks like you are talking about the formulas in columns D and E.
Did you adjust these formulas like I mentioned at the bottom of post 10 (the one that you marked as the solution)?
Your original formulas were relative references, and not absolute references, so as you copy formulas down the page, they will no longer line up.

I did not adjust these i was unsure on how I was supposed to refrence those.

and yes columns d, e, and k on the "PO Log" picture.

 

[Joe4]

I did not adjust these i was unsure on how I was supposed to refrence those.

That is necessary in order to get the code to work properly!

It is simply a matter of turning on the Macro Recorder, and re-entering the formula using absolute references (putting "$" in front of the column and row references), and taking the code that was created and putting that over the formula you have now.

If you don't feel comfortable doing that, can you tell me the exact formulas in cells D9 and E9 on your original sheet?

 

[cc11]

Yes if you dont mind... these were the exact formulas that i had in there.

=@'PO (1)'!G6:I6 for D9


=@'PO (1)'!H11:I11 for E9

=@'PO (1)'!I51:I52 for K9

 

[Joe4]

=@'PO (1)'!G6:I6 for D9


=@'PO (1)'!H11:I11 for E9

=@'PO (1)'!I51:I52 for K9

What exactly are you trying to return in those three cells?
You have entered a whole range of numbers, which doesn't really make much sense.
Are you trying to do a SUM or a COUNT? If so, you need to use one of the SUM or COUNT functions.

 

[cc11]

so for D9 it will simply be a name that is entered from the PO tab in cells G6,H6,I6 that are all merged together.

for E9 it is the same thing but it will be a number that is entered into cells H11 and I11 that are merged togther

and for K9 it is also the same thing and will be the number thats enetered into cells I51 and I52 that are mereged together

 

[Joe4]

If I can give you some advice, get rid of merged cells and never use them again!
I am serious. They are one of the worst inventions in Excel are cause all sorts of issues VBA, sorting, and other things. Most experienced programmers avoid them like the plague!
For centering across rows, there is a much better way of gaining that visual effect without all the issues caused by merged cells. See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

So after you get rid of the merged cells, the VBA code formulas for columns D and E should now look like:

    Range("D" & lr).FormulaR1C1 = "='" & ws & "'!R6C7"
    Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R11C8"

and for column K would be:

    Range("K" & lr).FormulaR1C1 = "='" & ws & "'!R51C9"

So for column K, the formula is referencing cell I51. Column "I" is the 9th column, and it is row 51.
Hence the: R51C9

You will probably need to do the same thing for the other columns (C, G, H, I, and J).

 

[cc11]

Joe4 I can't thank you enough but thank you this fixed everything and it is all working smoothly now!! And thank you for the advice for the merged cell's.
I am going to read Tom's article here shortly.

Thanks again for all your help!!