dynamically naming ranges

[KimC]

I have a workbook in which I want to run the same macros on each sheet. The
macros contain range names, so I know I will have to rename the ranges for
each sheet. Is there a way to programmatically change the macro range names
so that I can use the same set of macros for each sheet (there are MANY
sheets)? I am thinking something like incorporating the sheet name in the
range name when it is created by the code. I realize that the range names
that are created will have to be deleted after the macros are run to avoid
creating _1, _2, etc versions. Any help appreciated.

 

[joel]

use set statements in your code


for each sht in sheets
Set Range1 = .Range("A14")
Set Range2 = .Range("B7:H4")
Set Range3 = .Range("G1:K4")

next sht

 

[KimC]

Thank you so much--I'll try that

use set statements in your code


for each sht in sheets
Set Range1 = .Range("A14")
Set Range2 = .Range("B7:H4")
Set Range3 = .Range("G1:K4")

next sht

 

[Arvi Laanemets]

Hi

Define the named range Test, with Source=INDIRECT("$A$1:$A$10")

Now, the named range Test returns cell range from CURRENTLY ACTIVE SHEET.

So define your range using INDIRECT and without any sheet reference. And
then in your script to switch between ranges on different sheets, you
activate the sheet, reread the named range, and make your calculations with
data from this sheet, then you avtivate next sheet, etc.


Arvi Laanemets