1

I am trying to collect data from a cell from one sheet(3 Combined I) and pass it to a cell in another sheet(5 Gas I) using vba via a command button. This is what I have tried to use but it insteads copies the formula within that cell from sheet 3 Combined I?

Private Sub CommandButton1_Click()

Worksheets("3 Combined I").Range("e23").Copy Destination:=Worksheets("5 Gas I").Range("d10")

End Sub
David Ranieri
  • 39,972
  • 7
  • 52
  • 94
Jonathan Clark
  • 47
  • 1
  • 10

1 Answers1

3

Please try

Private Sub CommandButton1_Click()
  Worksheets("5 Gas I").Range("d10").value = Worksheets("3 Combined I").Range("e23").value
End Sub

You can also try the PasteSpecial method of Range object.
Please see Excel VBA Copy Paste Values only( xlPasteValues )

Updated

@nbayly recommands Value2 property of Range object instead of Value property.

Private Sub CommandButton1_Click()
    Worksheets("5 Gas I").Range("d10").value2 = Worksheets("3 Combined I").Range("e23").value2
End Sub

Reason as: What is the difference between .text, .value, and .value2?

Community
  • 1
  • 1
PaichengWu
  • 2,649
  • 1
  • 14
  • 28
  • worked perfectly, was expecting some complex special paste code completely overlooked the idea you could write that the cell is equal to previous one. Brilliant, thank you. – Jonathan Clark Aug 25 '16 at 21:40
  • 1
    You can also try the `specialPaste` method in `Range` object. But I prefer the most simple one as `range(xxx).value = range(yyy).value`. – PaichengWu Aug 25 '16 at 21:45
  • 1
    I would suggest using `range(xxx).Value2 = range(yyy).Value2` instead, as this provides more consistently the appropriate answer. `.Value` has the drawback of being subject to some formatting, changing the value that you are passing (ie rounding to 2 digits if cell is formatted as currency). – nbayly Aug 25 '16 at 21:47
  • @nbayly Yes, your are right. I just checked [http://stackoverflow.com/q/17359835/6202343](http://stackoverflow.com/q/17359835/6202343). – PaichengWu Aug 25 '16 at 21:57