# Button to copy sum to next row on another sheet

Closed
Stokesy13 - Jan 22, 2016 at 05:24 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jan 22, 2016 at 07:59 PM
Hi,

I think i have almost go this (pretty new with vba).
On first sheet 'comparison' i have a few variables which can be changed then the sum of these, in this case F10-F6, needs to be copied to the next free row on next sheet 'totals'. From this sheet i can use a sum to keep track of running totals..

So far i have got

Sub button1_click()
Dim ws1 as worksheet, ws2 as worksheet
Dim destrow as long
Set ws1 = sheets("comparison")
Set ws2 = sheets("totals")
Destrow = ws2.cells(rows.count, "A".End(xlup).row + 1
Ws1.range("F15").copy ws2.range("A" & destrow)
End sub

However all it pulls rough to next row is Ref#

Any help as to where i need to tweak?

Thanks
Related:

## 2 responses

vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 22, 2016 at 06:14 AM
Hello Stokesy13,

I would say that the cell F15 (the sum of F6:F10 ?) has a formula in it which would explain the #REF error.

Hence, try the following:-

Change

`ws1.range("F15").copy ws2.range("A" & destrow) `

to
```ws1.range("F15").Copy
ws2.range("A" & destrow).PasteSpecial xlPasteValues```

and in this line:-

`Destrow = ws2.cells(rows.count, "A".End(xlup).row + 1`

you are missing a parenthesis. Hence change it to:-

`Destrow = ws2.cells(rows.count, "A").End(xlup).row + 1`

Also, watch your upper and lower cases throughout the code. For example, the sheet names in the code need to be exactly the same as the tab name.

I hope that this helps.

Cheerio,
vcoolio.
Thank you for the very prompt reply. I did not expect that so quick!

I have made that change but it says an error (and highlights that row) with the
.PasteSpecial xlPasteValues ??
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jan 22, 2016 at 07:59 PM
Hello Stokesy,

I've just set up a test work book based on what I believe yours looks like and the code works just fine. Have a look at the following link to my test work book:-

https://www.dropbox.com/s/4tneu9nj9j8ype2/Stokesy13.xlsm?dl=0

You can test the code for yourself in the test work book.

Here is the code again:-

```Sub TransferData()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim destrow As Long
Set ws1 = Sheets("Comparison")
Set ws2 = Sheets("Totals")

destrow = ws2.Cells(Rows.Count, "A").End(xlUp).Row + 1

ws1.Range("F15").Copy
ws2.Range("A" & destrow).PasteSpecial xlPasteValues
Sheets("Totals").Select

End Sub```

If it still doesn't work for you then it would be best if you could upload a sample of your work book to DropBox (as per my above link) and then posting the link to your file back here. We can then have a look and try to sort out the problem for you. Be careful with any sensitive data.

Cheerio,
vcoolio.