Button to copy sum to next row on another sheet Closed

Question

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

vcoolio · Answer

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.

vcoolio · Answer

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.

Button to copy sum to next row on another sheet

2 responses

Similar discussions

Subscribe To Our Newsletter!