Set Command For a Worksheet

[MikeG]

I have a cell in my spreadsheet that I have given the range name "Sh1_Name". Currently the value in the cell is the word "Product".

In my macro I want to tell Excel to set OpsSht(1) to the "Product" worksheet, but cannot come up with the right code. I have...

Dim OpsSht(10) As Worksheet
Set OpsSht(1) = Sheets(Range("Sh1_Name"))

... but the second line does not work. Could someone help?

Thanks,

MikeG

 

[Dryver14]

Mike,

Are OpsSht 10 and OpsSht 1 the same thing?

Dim OpsSht1 as Worksheet
Set OpsSht1 = Worksheets("Product")

 

[MikeG]

Mike,

Are OpsSht 10 and OpsSht 1 the same thing?

Dim OpsSht1 as Worksheet
Set OpsSht1 = Worksheets("Product")

Thanks Dryver.

Actual they are not. I have 9 other sheets so for example I want OpsSht(1) as "Product", OpsSht(2) as "Customer" and so on. But I want the user to be able to define these names in the spreadsheet, not in the macro - because the names can change from time to time.

Mike