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.

use set statements in your code


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

next sht

"KimC" wrote:

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.

Thank you so much--I'll try that

"joel" wrote:

use set statements in your code


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

next sht

"KimC" wrote:

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.

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



"KimC" wrote in message
...
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.