Solved: copy sheet and change formula reference

[ANBgrafix]

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.

[vba]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[/vba]
THANX IN ADVANCE!
ANB

[mikerickson]

Both formulas refer to cells on the same sheet as the formula. I'd use

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

[Bob Phillips]

Your code is not the same as the comments at the top. What exactly do you want to do?

[ANBgrafix]

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]

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]

THANK YOU!THANK YOU!THANK YOU! Works perfect.

[ANBgrafix]

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]

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 line[VBA]With ActiveSheet[/VBA](remove the existing With)

[ANBgrafix]

Thank!Thank you!Thank You! Once again. Tested works!
ANB

[mikerickson]

I'm glad to have helped.