Range Question

[Ray Batig]

Greetings,

With some help from this group, I have a macro that copies a sheet several
times to the same workbook and then renames them. With the copy, all the
ranges in the sheet are copied also. This is great since when sheets is
activated several macros work without any chances. Now however, I am having
problems when I need to access these ranges and the parent sheet is not
active. Another interesting thing is that the copied ranges do not show up
in the list when you use Insert > Name > Define command tree.

What I am trying to get to is to be able to use the ranges in a Match
structure, so they need to end up as strings. In my previous coding, I used
string variables to make the coding very versatile.

Dim ItemNameRng As String

ItemNameRng = "Data"

I have tried getting the worksheet name into a variable wrksheet and adding
it to the range;

ItemNameRng = wrksheet + "Data"

Similarly I have tried;

ItemNameRng = Worksheets(wrksheet).Range( "Data")

None of these, and other variations, have worked.

Any ideas on how to fix this?

Thanks in advance!!

 

[Bob Phillips]

I am not sure what ItemNameRng is, a range object or a range address. If the
former, use

Set ItemNameRange = Worksheets(wrksheet).Range( "Data")

if the latter, use


ItemNameRange = Worksheets(wrksheet).Range( "Data").Address