Named Range across several worksheets

[Ed Bitzer]

I need a named range for a cell on each of 12 (months) worksheets that
applies to the selected sheet, for example Cash on the selected sheet.
If I select all the sheets (group) and enter the range name, it is
specific for the sheet entered, such as Jan.Cash. Is there away around
entering named ranges that refer to each sheet without having to enter
into each sheet individually? I did find that if I copy Jan and then
rename the sheet, the range assumes the sheet name. This is fine for
initial creation but not latter updating.

Ed

 

[Ed Bitzer]

Ron,

Thanks so much - works as advertised.

I was in the process of learning and incorporating several macros into
my personal asset sheets to automate manual changes in formulas that
were necessary every month. If I copied Feb to Mar then I had to change
references in the Mar sheet from Jan to Feb for prior month info. I got
bogged down simply referring to the cell I wanted changed and I now can
name those ranges once and for all.

Now if you happened to read this response, care to throw in how I can
prevent your macro from processing "each sh in this workbook" and limit
it to only Jan thru Dec - I have a couple of sheets at the end for
annual summaries and do not want the range names there. That clues you
in that I am just getting into macros - although not a bad VB programmer
and VBA behind Access.

Have checked out your web site and will spend some time leaning from the
code offered.

Thanks again,

Ed

 

[Ron de Bruin]

You can use a array like this Ed

For Each sh In Sheets(Array("Sheet1", "Sheet3"))