Difficulties with named range

[Gerry]

I have a single cell named BRHCTS in several excel files. It
indicates the bottom-right-hand-corner of the range I am interested
in. The upper right hand corner is always the cell A10. Therefore,
the range I want to copy from each of my files (into a consolidated
worksheet) is "A10:BRHCTS".

Can anyone advise me how I can do that without opening the source
files?

Thanks.

 

[Barb Reinhardt]

If you want to select/copy/do something on the range in vba, you'll need
something like this

Range("A10:"&Range("BRHCTS").address)

If you want to copy these ranges from multiple closed workbooks, the only
way is to open them. If that's not what you're asking, come back.

I've used code that copies multiple worksheet ranges into one worksheet, but
I can't seem to find it right now. Maybe someone else knows the link to it.