PDA

View Full Version : Solved: copy sheet and change formula reference



ANBgrafix
06-14-2008, 03:05 PM
IS THERE A WAY TO COPY THE CUURRENT SHEET TO A NEW SHEET AT THE END OF A WORKBOOK AND HAVE IT AUTOMATICALLY COPY THE VALUES OF 2 CELLS IN THE PREVIOUS SHEET AND PASTE THEM IN 2 DIFFERENT CELLS IN THE NEW WORKSHEET.

The Macro below was recored to give you an idea of what i was trying to do.
1 "Totals" is the first sheet in the workbook.
2 "Totals (2)" is the newly copied worksheet.

Sub SheetCopy()
' SheetCopy Macro
' Copy the active sheet to the end of a workbook then while the copied
' sheet is active copy the values of B29:C29 and paste the values to new
' sheet B2:C2

'copy to sheet2
Sheets("Totals").Select
Sheets("Totals").Copy After:=Sheets(1)
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUM(Totals!R[27]C+1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM(Totals!R[27]C+1)"
Range("C3").Select
'copy to sheet 3
Sheets("Totals (2)").Select
Sheets("Totals (2)").Copy After:=Sheets(2)
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SUM('Totals (2)'!R[27]C+1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=SUM('Totals (2)'!R[27]C+1)"
Range("C3").Select
End Sub
THANX IN ADVANCE!
ANB

mikerickson
06-15-2008, 11:42 AM
Both formulas refer to cells on the same sheet as the formula. I'd use

.FormulaR1C1 = "=R[27]C+1" in both places in the code

Bob Phillips
06-15-2008, 11:49 AM
Your code is not the same as the comments at the top. What exactly do you want to do?

ANBgrafix
06-16-2008, 10:33 AM
Trying to copy the active sheet "Totals" and paste a new sheet "Totals (#)" at the end of the workbook.
With the active sheet now being "Totals (#)" the values of "B2:C2" would be the value of "B29:C29 +1" from the previous sheet.

mikerickson
06-16-2008, 12:21 PM
This will do what you describe

With ThisWorkbook.Sheets("Totals")
.Copy after:=.Parent.Sheets(.Parent.Sheets.Count)
ActiveSheet.Range("B2:c2").FormulaR1C1 = _
"=1+" & .Range("B29").Address(True, False, xlR1C1, True, .Range("b2"))
End With

Rem ActiveSheet.Range("B2:C2").Value = ActiveSheet.Range("B2:C2").Value
That will put formulas in the new B2:C2, if you want values instead of formulas, un-comment the last line.

ANBgrafix
06-16-2008, 12:39 PM
THANK YOU!THANK YOU!THANK YOU! Works perfect.

ANBgrafix
06-17-2008, 05:27 PM
mikerickson

The code was working great! For some reason the code now only wants to copy the values of the first sheet "Totals" to second sheet "Totals (2)", which is fine but, when I select the "Totals (2)" now being the active sheet and run the code the "Totals (2)" is duplicated without changing the cells values?
Any Ideas?

mikerickson
06-17-2008, 05:58 PM
The code I wrote will copy Totals. It will not copy Totals (2). What you are seeing is another copy of Totals. If you want the routine to operate on the ActiveSheet (rather than Totals), start with this lineWith ActiveSheet(remove the existing With)

ANBgrafix
06-17-2008, 06:53 PM
Thank!Thank you!Thank You! Once again. Tested works!
ANB

mikerickson
06-17-2008, 07:48 PM
I'm glad to have helped.