MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copying data calculated by a formula and pasting to a different worksheet


Posted by John Haines on October 10, 2001 5:13 AM

I used the sum formula to add up some numbers, now I want to copy this total to a different worksheet.


Posted by RONALD on October 10, 2001 5:29 AM

Try this:-

Sub CopyDiffSheet()
Sheets("Sheet1").Range("A5").Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

Rename sheet1 to the sheet to copy from
Change A5 to the correct cell where the SUM formula is
Rename sheet2 to the sheet to copy to
Change A1 to the correct cell to copy to

Posted by John Haines on October 10, 2001 5:54 AM

That works but, when I go to sheet 1 and change on of the numbers I am adding up, the number on sheet 2 does not change. I need to have this change every time I add or subtract from my origional formula

Posted by RONALD on October 10, 2001 6:08 AM

I don't know if you want to use a macro with it?
If you do try this:

Sub CopyDiffSheet()
Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A5")
End Sub

In case you don't want to use a macro put in the cell you want to copy to the following formula:

=Sheet1!A1

But just like the last time change it to the right sheets and cells.

Hope it works?

Regards,

Ronald

Posted by John Haines on October 10, 2001 6:20 AM

=Sheet1!A1


That is what I needed


Thanks