Macro/Button To Generate New Line and Number

[DanA1509]

Hi All,

I am looking to create a button that can be used to generate a new line in the spreadsheet that autopopulates the first collumn with a sequential number based upon the previous lines number.

I've been looking through the forum and haven't been able to locate anything close enough to what I am looking for or think is close enough, I'm honestly not entirely sure what I would be looking for.

If it can push the whole spreadsheet down a row, and then put the sequential number on the new row, any help would be greatly appreciated.

 

[William Day]

Assign this macro to your button. The code below assumes your first new invoice will start in cell A2. Adjust ranges (in red) as needed.

Sub DanA1509()    
    Range("[COLOR=#ff0000]A2[/COLOR]").EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("[COLOR=#ff0000]A2[/COLOR]").Value = Range("[COLOR=#ff0000]A3[/COLOR]").Value + 1
End Sub

Hope this helps.
WD

 

[Mr Mahesh]

Hi DanA1509,

If your requirement is to add sequential number at the top of data, William's procedure works perfectly.
You can use the below code, if your requirement is to add sequential number at the bottom of the data.

Data Table:
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]SL No
[/TD]
[TD="width: 64"]Product
[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]xyz
[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]abc
[/TD]
[TD="align: right"]200
[/TD]
[/TR]
</tbody>[/TABLE]

Copy and paste the above table in cells A1:C3 in workbook. Insert code module and copy the below procedure into code module.

Sub InsertSeqNo()
Dim intLastRow As Long
 'Find out last row in column A
intLastRow = Range("A" & Rows.Count).End(xlUp).Row
 'Adding a sequential number at bottom of data 
Cells(intLastRow + 1, "A") = Cells(intLastRow, "A") + 1
End Sub

Try running procedure, it inserts sequential number at bottom of data in Column A as below.

Thanks,
Mahesh

 

[DanA1509]

William, a big thank you for your help. The code you gave me was exactly what I was looking for.

Mahesh, thank you for your help as well.