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.