I am looking for a way to work with named ranges in Excel Macros, when the same macro is to be run on multiple named ranges.
I have written a Macro (Macro1) in a particular sheet (Sheet 1) which have named references (E.g. Aa1). Now I have to copy this sheet multiple times (Sheet 2, Sheet 3, etc). The named reference would get copied along with during the copying process (Aa1 in Sheet 2, Aa1 in Sheet 3).
When operations are done, it seems to reference only the first occurrence of the named range, i.e. Aa1 in Sheet 1 and not in Sheet 2 or Sheet 3. The same should be applicable while running the macro also.
To get over this and correctly reference the cells in each sheet, I can rename the named range accordingly - rename to Aa2 in Sheet 2, to Aa3 in Sheet 3, etc.
However, only the named range Aa1 would be referred in Macro1. How do I make the Macro run using some loop for all the named ranged? E.g. if i=2, the named range referred would be Aa2, if i=3, the named range referred would be Aa3, etc.
That is how does one replicate the same Macro across Sheet 2, Sheet 3, etc without copying it multiple times?
I have written a Macro (Macro1) in a particular sheet (Sheet 1) which have named references (E.g. Aa1). Now I have to copy this sheet multiple times (Sheet 2, Sheet 3, etc). The named reference would get copied along with during the copying process (Aa1 in Sheet 2, Aa1 in Sheet 3).
When operations are done, it seems to reference only the first occurrence of the named range, i.e. Aa1 in Sheet 1 and not in Sheet 2 or Sheet 3. The same should be applicable while running the macro also.
To get over this and correctly reference the cells in each sheet, I can rename the named range accordingly - rename to Aa2 in Sheet 2, to Aa3 in Sheet 3, etc.
However, only the named range Aa1 would be referred in Macro1. How do I make the Macro run using some loop for all the named ranged? E.g. if i=2, the named range referred would be Aa2, if i=3, the named range referred would be Aa3, etc.
That is how does one replicate the same Macro across Sheet 2, Sheet 3, etc without copying it multiple times?
