Formula/Code to create a serial number in a new row

[STEVENS3010]

Hi all,

Is anybody able to help me figure out how to create a formula or code that could be used to create a sequential serial number in a table of data.

I have a spreadsheet that will be used to log customer enquiries. The sheet has a number of columns of data capture, with the sequential serial numbers in column A. Ideally, I would like to use a macro which will allow me to press a button to generate a new serial number in column A on the next row at the bottom of the data range.

For example, cell A2 would show ref “Prof-Enq0001”. Then when the button is pressed it inserts a new row underneath and automatically inputs “Prof-Enq0002”. If the button is pressed again, the same thing happens and the ref number input below is “Prof-Enq0003”

Is this possible? If so, could somebody point me in the right direction as to how exactly?

Thank you

 

[Michael M]

This will do what you require, BUT.. why not simply drag the above row down one by using the fill handle in the bottom right of the cell above ??

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & lr - 1).AutoFill Destination:=Range("A" & lr - 1 & ":A" & lr), Type:=xlFillDefault
End Sub

 

[STEVENS3010]

This is great - thank you.

 

[Michael M]

Glad to help...??