Previous Topic Next Topic

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

Answer 1 / 2
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.
0
Stokesy13
Jan 22, 2016 at 12:37 PM
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..
0
Answer 2 / 2
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.
0