Duplicating a worksheet but having the formula reference change..

[igrajeda]

For instance,

I have a worksheet tab labeled "Draw01" & "Draw02". I have information from Draw01 put into Draw02 with a formula.
Example: In the Draw02 worksheet I have a cell referencing information from two cells in Draw01

=sum(Draw01!D3+Draw01!D4)

When I create a new tab I will label it Draw03 and I would like to duplicate the whole worksheet and have the formula on the new tab "Draw03" to show:

=sum(Draw02!D3+Draw02!D4)


When I duplicate the tab Draw02 it does not change the formula to get the information from Draw02. It keeps everything referencing Draw01. Do I have to create a code in VBA to do this or is there another way?

Thanks!

 

[mfexcel]

Try using Find and Replace:

In your copied sheet,
Find: Draw01!
Replace: Draw02!

For instance,

I have a worksheet tab labeled "Draw01" & "Draw02". I have information from Draw01 put into Draw02 with a formula.
Example: In the Draw02 worksheet I have a cell referencing information from two cells in Draw01

=sum(Draw01!D3+Draw01!D4)

When I create a new tab I will label it Draw03 and I would like to duplicate the whole worksheet and have the formula on the new tab "Draw03" to show:

=sum(Draw02!D3+Draw02!D4)


When I duplicate the tab Draw02 it does not change the formula to get the information from Draw02. It keeps everything referencing Draw01. Do I have to create a code in VBA to do this or is there another way?

Thanks!