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!
 

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
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to work through all the issues!
 

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
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?
 

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

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.
 

Attachments

  • po log.jpg
    po log.jpg
    248.8 KB · Views: 1
  • po .jpg
    po .jpg
    180.1 KB · Views: 1

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

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

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
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

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
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Try placing this just before the "End Sub" line at the end of your code, and see if it does what you want:
VBA Code:
'   Populate cell H2 on new sheet with formula to pull from column C
    Sheets(ws).Range("H2").Formula = "='PO (0)'!C" & lr
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,154
Latest member
gm_jagath

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