Hi Vasant,
The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.
I suspect 3D ranges are a kludge that MS cobbled together in response
to what it must have perceived as a competitive threat from one of the
other spreadsheet packages. It's a kludge since a 3D range violates
the XL object model. A range's parent is a worksheet. But, that,
obviously, is not true for a 3D range. So, it kinda works where it
works. I cannot recall MS making any enhancements to the 3D capability
since it was first introduced, which shouldn't really be a surprise
since it would require redoing a core component of the OM.
Further, it should not come as a surprise that it doesn't play well
with VBA. For example, with a single-sheet named range, the following
works just fine:
?application.worksheetfunction.Sum(range(activeworkbook.Names(2).Name))
But, replace Names(2) with Names(1) and the result is 'Application-
defined or object-defined error.' Yes, Names(1) is the 3D range and
Names(2) is a single-sheet name.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions