VBA for Unique IDs

[dannyok90]

Hi all, I've scoured the forum for a solution to this. I know there are various threads that are related but either my VBA skills aren't quite good enough or they don't quite match my requirements.

basally I have the following script:

Sub AddRow()
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
Application.ScreenUpdating = False
LastRow = sht.Range("A:A").CurrentRegion.Rows.Count
Range("Sheet1!A26").EntireRow.Copy
Range("A" & LastRow + 1).EntireRow.Insert
Range("A" & LastRow + 1).EntireRow.Hidden = False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

it simply adds a new row to my sheet by copying a template row from one sheet and pasting it in another. after the paste id like to add a unique id with a prefix in the first cell. probably XYZ/ddmmyyhhmmss. So if I delete an entry later, the id below as the cells move up will remain the same.

Thanks!
Dan

 

[jmacleary]

Hi there.

Add the red line of code below:

Sub AddRow()
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ActiveSheet
Application.ScreenUpdating = False
LastRow = sht.Range("A:A").CurrentRegion.Rows.Count
Range("Sheet1!A26").EntireRow.Copy
Range("A" & LastRow + 1).EntireRow.Insert
Range("A" & LastRow + 1).EntireRow.Hidden = False
[COLOR=#ff0000]Range("A" & lastrow + 1).Value = "XYZ/" & Format(Date, "ddmmyy") & Format(Time, "hhmmss")[/COLOR]
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[dannyok90]

Brilliant!

Thanks very much! - one for my growing notebook of code!

Dan