Copy and Paste from one sheet to another

[jackofalltrades2011]

Hello ALL!

my code is:

Sub ATA_total()
'
' Total column P of sheet(3) and paste total in Sheet(1)
'
Dim end_row_P As Long

end_row_P = Range("P1048576").End(xlUp).Row
Sheets(3).Range("P" & end_row_P + 2).Formula = "=SUM(P3:P" & end_row_P & ")"
Range("P" & end_row_P + 2).Select
Selection.Copy
Application.Goto Sheets(1).Range("B28")
Range("B28").Select.Paste
Selection.Paste

End Sub


I am trying to copy a cell value from one sheet to another sheet.

the goal is to get Application.Goto Sheets(1).Range("B28")= Range("P" & end_row_P + 2).Select

 

[p45cal]

try changing:
Range("P" & end_row_P + 2).Select
Selection.Copy
Application.Goto Sheets(1).Range("B28")
to:
Sheets(1).Range("B28").value=Sheets(3).Range("P" & end_row_P + 2).value

and remove altogether:
Range("B28").Select.Paste
Selection.Paste

 

[jackofalltrades2011]

Thanks!!!

It copies but not the sum...it is showing zero!

I believe it has to be a SPECIAL past of the value instead of just a copy and paste.

 

[p45cal]

The code is the same as PasteSpecial|Values. since it's setting the .Value of a range to the .Value of another, not the .formula property.
My concern would be the sheet references. If you add or delete a sheet before sheet(3) it could throw the refs. out of kilter.
Just for verification purposes, instead of the likes of
Sheets(3).range... and Sheets(1).range...
try temporarily using:
Sheets("TheSheetNameAsOnItsTab").range... and Sheets("TheOtherSheetsNameAsOnItsTab").range...
throughout.

 

[jackofalltrades2011]

Still getting a zero on both sheets...revision looks as follows:

Sub ATA_total()
'
' Total column P of sheet(3) and paste total in Sheet(1)
'
Dim end_row_P As Long

end_row_P = Range("P1048576").End(xlUp).Row
Sheets("Non-Intercompany Details").Range("P" & end_row_P + 2).Formula = "=SUM(P3:P" & end_row_P & ")"
Sheets("Non-Intercompany Details").Range("B28").Value = Sheets("Overall Summary").Range("P" & end_row_P + 2).Value

End Sub

~I am using Excel 2007, if it matters~

 

[p45cal]

This line needs qualifying as currently it refers to whatever happens to be the active sheet (or perhaps, if the code is in a sheet's code module, that sheet):
end_row_P = Range("P1048576").End(xlUp).Row
to:
end_row_P = Sheets("whateversheetitson").Range("P1048576").End(xlUp).Row