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

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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