Very Simple but I can't work it out! - Sequential Unique Identifiers

[ClimoC]

Hello...

I have this code atm:

Range("D4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("E:E").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D:D").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, Lookat _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("D9:G9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

etc etc etc... over and over for different cells. What is happening is that it is taking the values from different individual cells scattered around sheet1, and pasting them in their appropriate columns in sheet2 at the next blank row (making a tidy database out of sheet1 which is an aethestically-pleasing input form)

In return to this, I also have a 'review' macro...:

Sheets("Sheet2").Select
Columns("I:I").Select
Selection.Find(What:=Range("sheet1!K10").Value, After:=ActiveCell, LookIn:= _
xlFormulas, Lookat:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
xlNext, MatchCase:=True, SearchFormat:=True).Activate

reviewrow = ActiveCell.Row


Range("sheet1!D4") = Range("Sheet2!E" & reviewrow).Value
Range("sheet1!D5") = Range("Sheet2!D" & reviewrow).Value

etc etc... it pastes the information entered back into the input-fields where they entered them (so that another macro, 'update', can change fields if they wish). Cell Sheet1!K10 is a linkedcell for the listbox which displays the entries created.

Where I'm having problems is that the first macro (the one that writes to the database) pastes to the next blank cell in each column . This means that if you miss a field in your first entry, call the field 'time' for example, if you enter a time on your second entry, the macro puts the 'time' you entered for the second one on the first blank cell it can find in the time column - so you end up with data squashing up together.


I have decided that the best way to avoid all of this is to assign a unique identifier number for each entry sent to the database. I'm going to have these numbers (1-*end#) in row X of sheet2. This way, I can reroute my listbox and my macros to search for each entry by it's unique identifier and then copy and paste across the various bits of info in cells in the corresponding row, whereas right now it is searching through the database for a text word (Title of the item).

So what I need is to add onto my first macro at the top: A line or two which will type numbers from '1' incrementally up to *whereever* whenever the macro starts doing the new row.

For anyone's reference, all information starts pasting from row 6 of sheet2 downwards.

I'd really appreciate this if someone knows a quick and easy solution!