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.