Named Ranges on Multlple Worksheets

**ptmuldoon** · 09-28-2014, 01:40 PM

I've recently converted a number of ranges to Named Ranges to try and 'protect' if the user adds any rows or columns to the sheet.

I have the below code basically 5 different times, for 5 separate spreadsheets.

I had thought that by also assigning named ranges to everything that I could then possible combine all the named ranges together into one singe macro. However, those attempts fail with a Method 'Range' of object '_Global' failed.

Essentially, I'm trying to learn if you can combine multiple Named Ranges from separate worksheets into 1 macro, or if you need to Select each sheet separately. Just trying to learn and improve. I thought about also trying to learn and create a 2 dimensional array of the sheet name and named ranges for each, and then loop it that way.

Or assign separate 'rows_rng' and 'col_rng' for each sheet?

The below works fine. Only trying to learn why adding names ranges from other sheets will fail (I did removed the Sheets.Select when trying to add additional named ranges from other sheets)

Please Login or Register  to view this content.

**JBeaucaire** · 09-28-2014, 01:51 PM

You can perhaps play with the UNION(range1, range2) function, but I think creating a "range" is limited in scope to one parent object. You will most likely have to resort LOOPING through the various ranges on the various sheets and adding them to your chart specifically as a new series for each range. Not my expertise, yet.

**shg** · 09-28-2014, 04:10 PM

Originally Posted by JBeaucaire

... a "range" is limited in scope to one parent object ...

Limited to cells on a single worksheet, absolutely, yes.

Even 3D ranges (a common range across two or more contiguous worksheets), which are accepted by some functions (e.g., =SUM(Sheet1:Sheet2!A1:A3)) are accessible only via the UI, not in VBA; you can't pass a 3D range to a UDF.

**ptmuldoon** · 09-28-2014, 07:53 PM

Thanks guys and to simply

This below which includes 2 named ranges from 2 different worksheet fails because VBA can't handle 3D Ranges? Am I understanding correctly?

Please Login or Register  to view this content.

**shg** · 09-28-2014, 08:08 PM

Yup .

**JBeaucaire** · 09-28-2014, 09:27 PM

And if those ranges WERE on the same page I think your results would not be what you wanted.

If you want make a range of only the cells A1:B2 and E4:F5, a total of 8 cells, your syntax above:

Please Login or Register  to view this content.

... would result in a range of A1:F5, a total of 15 cells. Not what you want.

To get JUST the individual ranges together without the extra cells in between, use the UNION method:

Please Login or Register  to view this content.

Now THAT is only 8 cells still. All of that above holds true for named ranges, too.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**shg** · 09-28-2014, 11:15 PM

To get JUST the individual ranges together without the extra cells in between, use the UNION method:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Named Ranges on Multlple Worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Re: Named Ranges on Multlple Worksheets

Thread Information

Users Browsing this Thread

Similar Threads

Define only 2 named ranges from a list of named ranges...

Copying worksheets, to inherit named ranges from destination workbook?

[SOLVED] Need to know limits on naming files, named ranges and worksheets in Excel 2003

Excel 2007 : sum named ranges in different worksheets

Accessing Named Ranges on different worksheets

Bookmarks

Bookmarks

Posting Permissions