Numerical Order Code for Macro

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
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!
 
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.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Can you go to the "PO (1)" sheet, and post the formula that the code is putting in cell H2?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
I think we had it pulling from the wrong sheet.
See if this works for you:
VBA Code:
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
 
Upvote 0
That works perfect, exactly what I needed. Thanks again for all your help!!!!!

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

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top