Updating references in copied excel work sheet
 

I need to add pages to a workbook. The name of the new sheet is not
important since it will be changed by the user and may be characters
or numbers. This macro was created by the recorder. It works except
for the fact that the cell references don't update when a new sheet is
copied from last new sheet. (i used sheets name "c" as an example) The
first new sheet c(2) is fine.The next new sheet still references sheet
c instead of sheet c(2). Is there an easy way of doing this? Thanks



ActiveSheet.Select
ActiveSheet.Copy Befo=Sheets(1)
Range("F1").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("B6:B33").Select
Selection.ClearContents
Range("I6:I12").Select
Selection.ClearContents
Range("M24").Select
ActiveCell.FormulaR1C1 = "=c!R[1]C"
Range("M39").Select
ActiveCell.FormulaR1C1 = "=c!RC+R[1]C[-2]"
Range("K41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+c!RC"

End Sub

Updating references in copied excel work sheet
 

Hi nsuh -

Try this modification:

Sub nsuh()

ActiveSheet.Select
nm = ActiveSheet.Name
ActiveSheet.Copy Befo=Sheets(1)
Range("F1").Select
ActiveCell.FormulaR1C1 = "=1+'" & nm & "'!RC"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=1+'" & nm & "'!RC"
Range("B6:B33").Select
Selection.ClearContents
Range("I6:I12").Select
Selection.ClearContents
Range("M24").Select
ActiveCell.FormulaR1C1 = "='" & nm & "'!R[1]C"
Range("M39").Select
ActiveCell.FormulaR1C1 = "='" & nm & "'!RC+R[1]C[-2]"
Range("K41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+'" & nm & "'!RC"

End Sub

----
Jay



" wrote:

I need to add pages to a workbook. The name of the new sheet is not
important since it will be changed by the user and may be characters
or numbers. This macro was created by the recorder. It works except
for the fact that the cell references don't update when a new sheet is
copied from last new sheet. (i used sheets name "c" as an example) The
first new sheet c(2) is fine.The next new sheet still references sheet
c instead of sheet c(2). Is there an easy way of doing this? Thanks



ActiveSheet.Select
ActiveSheet.Copy Befo=Sheets(1)
Range("F1").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("B6:B33").Select
Selection.ClearContents
Range("I6:I12").Select
Selection.ClearContents
Range("M24").Select
ActiveCell.FormulaR1C1 = "=c!R[1]C"
Range("M39").Select
ActiveCell.FormulaR1C1 = "=c!RC+R[1]C[-2]"
Range("K41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+c!RC"

End Sub

Updating references in copied excel work sheet
 

Jay, it works great
Thanks
-Kevin
On Apr 15, 8:00 pm, Jay wrote:
Hi nsuh -

Try this modification:

Sub nsuh()

ActiveSheet.Select
nm = ActiveSheet.Name
ActiveSheet.Copy Befo=Sheets(1)
Range("F1").Select
ActiveCell.FormulaR1C1 = "=1+'" & nm & "'!RC"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=1+'" & nm & "'!RC"
Range("B6:B33").Select
Selection.ClearContents
Range("I6:I12").Select
Selection.ClearContents
Range("M24").Select
ActiveCell.FormulaR1C1 = "='" & nm & "'!R[1]C"
Range("M39").Select
ActiveCell.FormulaR1C1 = "='" & nm & "'!RC+R[1]C[-2]"
Range("K41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+'" & nm & "'!RC"

End Sub

----
Jay



" wrote:
I need to add pages to a workbook. The name of the new sheet is not
important since it will be changed by the user and may be characters
or numbers. This macro was created by the recorder. It works except
for the fact that the cell references don't update when a new sheet is
copied from last new sheet. (i used sheets name "c" as an example) The
first new sheet c(2) is fine.The next new sheet still references sheet
c instead of sheet c(2). Is there an easy way of doing this? Thanks

ActiveSheet.Select
ActiveSheet.Copy Befo=Sheets(1)
Range("F1").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=1+c!RC"
Range("B6:B33").Select
Selection.ClearContents
Range("I6:I12").Select
Selection.ClearContents
Range("M24").Select
ActiveCell.FormulaR1C1 = "=c!R[1]C"
Range("M39").Select
ActiveCell.FormulaR1C1 = "=c!RC+R[1]C[-2]"
Range("K41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+c!RC"

End Sub- Hide quoted text -

- Show quoted text -