XL 97: Working with Named Ranges across multiple worksheets 1

[VBAjedi]

This may be simple, but it's driving me nuts! At the beginning of each sub, I call a routine that assigns each worksheet in my workbook to a Worksheet object for easy reference ("Set shOne = Worksheets("Sheet1&quot", etc.). I want to work with Named Ranges that exist on my worksheets. However, whenever I write code that deals with named ranges on sheets that are not active, my code errors out. For example:

Range("SDFilterCriteria&quot.Clear

errors out if the sheet containing this range is not active. I need to be able to clarify what sheet the range is on. I tried

shOne.Range("SDFilterCriteria&quot.Clear

but this also errors out. At the time of the error, the range does exist and shOne is a valid Worksheet object. The following works:

shOne.Activate
Range("SDFilterCriteria&quot.Clear

but activating the sheet is not an option (besides being unprofessional!). I get these errors with other types of commands as well (like "For each cell in Range("MyRange&quot&quot. What's the deal? I need to be able to manipulate the named ranges that exist on my worksheet objects.





VBAjedi

 

[SkipVought]

Hey VBA,

This will happen if you are coding in a SHeet Object and try to reference another sheet.

All the things that you are trying to do, work.

One other problem could be that if you have sheet objects like charts, OLE or drawing objects, AND the object has had the focus, I have had to activate the sheet (agghhhhh!) before referencing ranges. If this is the case, maybe some of our colleagues have a suggestion.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Skip,

No chart, OLE, or drawing objects involved here. And I am in a normal module, not a sheet object.

Knowing that my methodology is at least theoretically correct helps lots. It's probably a simple dumb oversight somewhere, and now I know I won't be wasting my time to look for it.

Consider this one solved unless I post back. Thanks for your input!

VBAjedi

 

[TonyJollans]

VBAjedi,

I commend your attitude that you consider the problem solved on the basis that it should work, and if you manage to make it work it would be great if you posted back.

Meanwhile, if you're still watching this thread, I'll throw in my bit because I have had similar problems. Whatever ought to work I have been totally unable to reference named ranges directly in a non-active workbook (book not sheet but I don't think there's much difference in theory).

With code in my own module the only way I have found to refer to a range in another workbook is with something like ...

Dim MyRange as Range
Set MyRange = OtherWorkbook.Names("MyRangeName").RefersToRange

After this I can then use MyRange as I expect to be able to.

Possibly related to this, I have not found any way to sort data on a sheet that is not active, but that's a problem for another time.

Enjoy,
Tony

 

[SkipVought]

Tony,

Here's the only way I know of to reference ranges in a CLOSED WORKBOOK...

    ActiveCell.Formula = "='C:\Summary.xls'!MyTestRange"

If you want to do it without storing formulas on the sheet, make an InputRange for receiving the data via formula and then read the value and write ti wherever you want.

   InputRange.Formula = "='C:\Summary.xls'!MyTestRange"
   MyValue = InputRange.Value
...

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

I think there IS a difference in how you reference named ranges on non-active sheets in the current workbook and how you reference sheets in another workbook. I am trying to do the first of those two. The following approach IS working for me now (sure enough - I had a dumb oversight that was causing my error. I had range dimensions that needed updating).

ShPD.Range("FDFilterCriteria&quot.Clear

where ShPD is a valid sheet object that I set ("Set ShPD = Worksheets("ProjectionData&quot"), and FDFilterCriteria is a valid named range that exists on that sheet. So far I have been able to execute any command on that range that you normally can execute on a range.

Hope this helps!


VBAjedi