Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copied from web page to excel sheet and cant remove from sheet | Excel Worksheet Functions | |||
copied from web page to excel sheet and cant remove from sheet | Excel Worksheet Functions | |||
Office 2007 Excel - Inserting Copied or cut rows from another work | Excel Discussion (Misc queries) | |||
Excel Work Book Copied Into Word With Paste Special, Excel Workboo | Excel Discussion (Misc queries) | |||
Updating Name References in Sheet | Excel Programming |