I have a workbook that I need to auto generate a unique daily 6 digit key number at the pr

Hello,
I need a bit of help please.
I have a workbook that I need to auto generate a unique daily 6 digit key number at the press of [Ctrl + C] and insert it into cell C:2 on a sheet called [Day].
I need a code that while working on [sheet Day] and [Ctrl + C] is pressed the code will look at another sheet [Keys] and the entire column [A] check all previously listed six digit key entry’s (this could be thousands of previous entry’s) and insert a new randomly chosen unique 6 digit key into [sheet Day cell C:2] that has never been used before and is not already listed on [sheet Keys column A] .
So no duplicates allowed.
I know that eventually all the six digit combinations will be exhausted, so when this point is reached I will clear column A and start fresh.

Thank you for your help in advance on this matter and taking the time to read this.

harry h.

(The time is fast approaching for all to know his name)

Thanks for looking Richard,

I can't do it this way as the six digits are codes for a combination safe and if I where to use your suggestion it could be open to foul play.
So I need the six digits to be completely randomly chosen.
Thanks for looking.
harry h.

(soon will know of the one and only true God who's name is Jehovah)

Please Login or Register  to view this content.

I am unable to get any of these suggestions running.

Maybe I haven't explained it clear enough so here go's. I have changed the sheet names so not to confuse me later.

I have a work book [Sheet Game]that has a button on it called [change code] when I press it it puts a six digit code into [C2] on this sheet.
I have another sheet called [Records] that has a long list of previously entered six digit codes in [column A] starting in cell A8 down to a possible 1000000 that are placed here each time a new code is generated from the Game sheet.

What I need is when I press the button called [Change code] the macro will look at sheet [Records column A] however long it might be and insert a unique six digit number into [C2] on [sheet Game] that is not already listed in [Records column A].

I hope this is a bit clearer. I can post my code or send a helper a copy of my work book if it will help any.

Thank you for the help so far.
harry h.

(All will soon have to know his name)

Thank you Richard,

Yes it works here perfectly. I will now place it into my worksheet and try it.
I will let you know the results.

harry h.

Hello Richard,

When I place the code you sent into my work book I get a:
[Run-time error`1004'
Method'Range' of object'_Global'failed

But I still need it to only change the code in [C2 sheet Game]
I have another button that updates the codes into [column A sheet Records] it just tags the next guessed code in the next available cell in [column A sheet Records]

harry h.

Hello Richard,

All sorted it was my fault I forgot to name the cell [Test] and I took out the last line of code starting with 'Sheets("Records").Range

It works great now.
Thanks very much for all your help and patience.

harry h.

(All will come to know his name who is Jehovah)