Button to copy sum to next row on another sheet [Closed]

Report
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
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

2 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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 ??
I think I had tried that before too, using Google..
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!