Macro to change the column references automatically
 

Hey people,

Just struggling with it for the past few days. I have quite a lot
formula in Sheet3 linked to Sheet1 and Sheet2. For example,
Sheet3 has
RangeA1 of sheet3 has a formula =Sheet1!A1
Range E2 of sheet3 has a formula =min(Sheet2!A:A)
etc... i.e. assorted cells in Sheet3 with formula relating to the
column A in sheet1 and sheet2

Most important thing is that formula in Sheet3 would relate to values
in column A of Sheet1 and Sheet2

When I make a copy of the sheet3 and get a sheet4, I would like to
relate the values of those cells to the one in column B. i.e.

Sheet4 has
Range A1 of sheet4 becomes = Sheet1!B1 becomes
Range E2 of sheet4 becomes = min(Sheet2!B:B)
etc...

Sheet5 will be related to column C so the formula in sheet5 becomes
Range A1 of sheet5 becomes = Sheet1!C1 becomes
Range E2 of sheet5 becomes = min(Sheet2!C:C)
......

Sheet26 will be related to column Z so the formula in sheet26 becomes
Range A1 of sheet26 becomes = Sheet1!Z1 becomes
Range E2 of sheet26 becomes = min(Sheet2!Z:Z)
.........................

Sheet 122 will be related to column DR so the formula in sheet122
becomes
Range A1 of sheet122 becomes = Sheet1!DR1 becomes
Range E2 of sheet122 becomes = min(Sheet2!DR:DR)

I have to do this for 122 sheets and things are getting crazy when I
do manually for me, due to lack of time. Please let me know if this
could be accomplished by a macro. Thank you very much

Macro to change the column references automatically
 

On Oct 30, 12:05 pm, wrote:
Hey people,

Just struggling with it for the past few days. I have quite a lot
formula in Sheet3 linked to Sheet1 and Sheet2. For example,
Sheet3 has
RangeA1 of sheet3 has a formula =Sheet1!A1
Range E2 of sheet3 has a formula =min(Sheet2!A:A)
etc... i.e. assorted cells in Sheet3 with formula relating to the
column A in sheet1 and sheet2

Most important thing is that formula in Sheet3 would relate to values
in column A of Sheet1 and Sheet2

When I make a copy of the sheet3 and get a sheet4, I would like to
relate the values of those cells to the one in column B. i.e.

Sheet4 has
Range A1 of sheet4 becomes = Sheet1!B1 becomes
Range E2 of sheet4 becomes = min(Sheet2!B:B)
etc...

Sheet5 will be related to column C so the formula in sheet5 becomes
Range A1 of sheet5 becomes = Sheet1!C1 becomes
Range E2 of sheet5 becomes = min(Sheet2!C:C)
.....

Sheet26 will be related to column Z so the formula in sheet26 becomes
Range A1 of sheet26 becomes = Sheet1!Z1 becomes
Range E2 of sheet26 becomes = min(Sheet2!Z:Z)
........................

Sheet 122 will be related to column DR so the formula in sheet122
becomes
Range A1 of sheet122 becomes = Sheet1!DR1 becomes
Range E2 of sheet122 becomes = min(Sheet2!DR:DR)

I have to do this for 122 sheets and things are getting crazy when I
do manually for me, due to lack of time. Please let me know if this
could be accomplished by a macro. Thank you very much

A loop to automate this column referencing would do. I am pretty sure
that I can write a macro to copy the sheets accordingly. Thanks

Macro to change the column references automatically
 

On Oct 30, 12:15 pm, wrote:
On Oct 30, 12:05 pm, wrote:





Hey people,

Just struggling with it for the past few days. I have quite a lot
formula in Sheet3 linked to Sheet1 and Sheet2. For example,
Sheet3 has
RangeA1 of sheet3 has a formula =Sheet1!A1
Range E2 of sheet3 has a formula =min(Sheet2!A:A)
etc... i.e. assorted cells in Sheet3 with formula relating to the
column A in sheet1 and sheet2

Most important thing is that formula in Sheet3 would relate to values
in column A of Sheet1 and Sheet2

When I make a copy of the sheet3 and get a sheet4, I would like to
relate the values of those cells to the one in column B. i.e.

Sheet4 has
Range A1 of sheet4 becomes = Sheet1!B1 becomes
Range E2 of sheet4 becomes = min(Sheet2!B:B)
etc...

Sheet5 will be related to column C so the formula in sheet5 becomes
Range A1 of sheet5 becomes = Sheet1!C1 becomes
Range E2 of sheet5 becomes = min(Sheet2!C:C)
.....

Sheet26 will be related to column Z so the formula in sheet26 becomes
Range A1 of sheet26 becomes = Sheet1!Z1 becomes
Range E2 of sheet26 becomes = min(Sheet2!Z:Z)
........................

Sheet 122 will be related to column DR so the formula in sheet122
becomes
Range A1 of sheet122 becomes = Sheet1!DR1 becomes
Range E2 of sheet122 becomes = min(Sheet2!DR:DR)

I have to do this for 122 sheets and things are getting crazy when I
do manually for me, due to lack of time. Please let me know if this
could be accomplished by a macro. Thank you very much

A loop to automate this column referencing would do. I am pretty sure
that I can write a macro to copy the sheets accordingly. Thanks- Hide quoted text -

- Show quoted text -

Hey guys, if not for 122 sheets, please help me with some 5-10 sheets.
I will replicate it for 122 sheets. Thanks