"Ian" skrev i en meddelelse
...
Hi Leo,

Thanks for a great solution, just what i was looking for, however, I've
hit
a bit of a problem and my VBA skills aren't good enough to determine the
problem.

I copied your code, as per instructions, into a new workbook and it
worked a
dream. Confident in my own limited skills I went to my workbook where I
wanted the origianl random numbers and repeated the process. Failure, I
can't
get past the following error message:

Runtime error '1004': Method 'Range' of object '_Worksheet' failed.

When I click Debug the following line of code is highlighted:

Set RandTableRange = Range(ActiveSheet.Name & "!" & RandTableName)

The "RandTable" is on the local worksheet as you suggest (Sheet10 in my
case), in fact I did no more than when I got it working in a new
workbook.

Is there something blindingly obvious I have missed?

Thanks in advance,
Ian.

Hi Ian (and Max :-)

Glad you could use it!

Here's version 3.0 with two more options. You have a choice of having
duplicates in the pool, and the range for the random numbers can concist
of non-contiguous areas. Please notice, that the sub "...BeforeDoubleclick"
is now inserted in "ThisWorkbook".

A.
The Rand data is set up in a named table in the proper worksheet(s).
The headings are *not* part of the name!

For example a named table could be H2:M12 (H1:M1 containing headings).

The name must be "RandTable" (without quotes) and it must be local, so
in sheet1 the name is sheet1!RandTable, in sheet2 the name is
sheet2!RandTable etc. Use Insert Name Define.

If you prefer another standard name for you RandTables, you must
edit the following line in "Workbook_SheetBeforeDoubleClick":

Const RAND_TABLE_NAME As String = "RandTable"

If you have spaces in a sheet name, the sheet name must be enclosed
in apostrophes (single quotes) like: 'Random numbers'!RandTable.

An idea is to use the underline character _ instead of space in
sheet names. The readability is the same, and you will never need to
use apostrophes in sheet names again.

If you doubleclick a sheet tab to rename the sheet, it's not necessary
to use single quotes. Excel does it for you.

B.