Copying data from one sheet to another using macros

0
votes

I have two sheets "ABC" & "XYZ" I would like to take the data from Sheet "ABC" D28:AZ28 (which is currently a =Sum formula) and put it on the bottom row (second column) of sheet "XYZ". I would also like the first Column of that row to be the date in Cell "A1" of sheet "ABC"

I haven't figured out the date part, and everytime I try a macros I get error message (appears that it is trying to alter the sum formula). 

Sub Copy_Method()
    Sheets("ABC").Range("D28:AZ28").Copy Destination:=Sheets("XYZ").Range("B2")
End Sub

And the Error I'm getting in the Cells in the "XYZ" Sheet is;

#REF!  [=Sum(#REF)]

Appreciate the assistance.

1
excelvba
#REF error means that the range formula is referencing does not exit. To learn that, open a new book. Enter formula in A16 =Sum(A5:A15). Copy the formula to B16, C12, D11. Check the ranges in those formulas they will move.. D11 will give #REF error as it will not find 11 rows to sum. – Naresh
Ok. So now I see what the issue is. How do I resolve it? – DeeTea

1 Answers

0
votes

Your code is copying the formulas with a relative cell reference. In other words, the "SUM" formula is trying to sum cells above row 1, which results in the #REF error.

You didn't specify exactly what you are intending to copy. If you just want the values (and not the formulas), you could paste the values only:

Sub Copy_Method()
    Sheets("ABC").Range("D28:AZ28").Copy
    Sheets("XYZ").Range("B2").PasteSpecial Paste:=xlPasteValues
End Sub