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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
Needing to basically have a code to the column B8 to B308 and have it increase by 1 each time I generate a new row with the macro button
 

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
If anyone can answer this question it would be greatly appreciated I need it ASAP.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
I think it might make more sense if we could see the data (in addition to the code you already posted).
Please provide a sample of what your data looks like, and what you would to happen, based on that sample data.
Just be sure to remove/dummy-up any sensitive data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

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

ADVERTISEMENT

Due to this being my work computer I did not have access to download the Add-In. I took screen shots of what I am trying to do.


The POLOG image is where you will see the tab labeled sequential number. I am needing it to generate the next number each time I click the new purchase order button. I am wanting it to go from 1to300. So I will click the NEW PURCHASE ORDER Button that I have this macro listed below assigned to and it will generate everything down and then in the Sequential number tab it will generate the next number so it will go 1, 2, 3, 4, 5 etc. to 300.


The MACRO image is where you will see the codes I have listed. So I am wanting to find a code to do what I mentioned above to add into that Module.
Here is my all of my coding from the picture:

Sub NEWPURCHASEORDER()
'
' NEWPURCHASEORDER Macro
' CREATE NEW PURCHASE ORDER
'
' Keyboard Shortcut: Ctrl+Shift+H
'
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("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




Let me know if there is anything I could do or answer to get this figured out! Thank you so much!
 

Attachments

  • macro.jpg
    macro.jpg
    225.8 KB · Views: 3
  • POLOG.jpg
    POLOG.jpg
    120.2 KB · Views: 3

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
So, if you want "0001" in B9, "0002" in B10, etc, you can use formulas like you are now for the other cells.
This would be the initial formula in cell B9 (or B8):
Excel Formula:
=TEXT(ROW()-8,"0000")
The formula will automatically increment when copied down.

Also note that you can make your code more efficient by getting rid of all the selects/activecells that the Macro Recorder uses.
Line couplets like this:
VBA Code:
Range("A9").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[1]"
can be simpliifed like this:
VBA Code:
Range("A9").FormulaR1C1 = "=R[-7]C[1]"
 

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

ADVERTISEMENT

so I put that in module 1 and it is saying
"Complie error:
Expected: line number or label or statement or end of statement"

Also with this code it would populate with the new row when I click the new purchase order button?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
OK, I thought you might be looking for dynamic code to end to the bottom of the list.
One question though. Does th following part need to be repeated every time a new row is added, or is that a one-time thing:
VBA Code:
Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)
What is going on there?
 

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
No I have it set up right now so that everytime I click the add new purchase order button a new row is populated down from the previous one however i cannot get the sequential number column/tab to populate in numerical order each time I click the button.

As for the code I believe it needs to be repeated each time? I am not really sure I am new to the whole macro world! lol.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
OK, I am not sure what you are doing with that section of code, as you have not posted sheet "PO (0)".

But here is dynamic code to insert that row with formulas:
VBA Code:
Sub NEWPURCHASEORDER()
'
' NEWPURCHASEORDER Macro
' CREATE NEW PURCHASE ORDER
'
' Keyboard Shortcut: Ctrl+Shift+H
'

    Dim lr As Long
    
'   ***NOT SURE WHAT YOU ARE DOING HERE***
    Sheets("PO (0)").Select
    Sheets("PO (0)").Copy After:=Sheets(3)
'   *************************************************************
    
    Sheets("PO Log").Select
    
'   Find last row with data in column A (Totals row)
    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 = "='PO (1)'!R[-3]C[3]:R[-3]C[5]"
    Range("E" & lr).FormulaR1C1 = "='PO (1)'!R[2]C[3]:R[2]C[4]"
    Range("G" & lr).FormulaR1C1 = "='PO (1)'!R[8]C[-4]"
    Range("H" & lr).FormulaR1C1 = "=SUM('PO (1)'!R[12]C[1]:R[39]C[1])"
    Range("I" & lr).FormulaR1C1 = "='PO (1)'!R[41]C"
    Range("J" & lr).FormulaR1C1 = "='PO (1)'!R[40]C[-1]"
    Range("K" & lr).FormulaR1C1 = "='PO (1)'!R[42]C[-2]:R[43]C[-2]"

End Sub
Note that you may have to edit the R1C1 range references on your formulas referencing your "PO (1)" sheet.
It looks like you recorded the formulas using relative range references, which allows the row/column references to float, instead of locking them down with absolute range references.
If the ranges should be "locked", then you can re-record those formulas using the absolute range references, and replace the formula part of each one (like I did for your column A formula).

See here for a better understanding of the different kinds of range references:
Here is an explanation of how the R1C1 references work in VBA (though you do not really need to know that if you wrote the formulas correctly, inserting $ where necessary and recorded that, as Excel will figure that part out for you): R1C1-Style Notation And FormulaR1C1 Property In Excel VBA
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,549
Messages
5,636,955
Members
416,952
Latest member
prakashkumar

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
Top