probably a simple one to your geniuses however I need a fresh pair of eyes as I've been on this a while and can't see my error. I'm merely wanting to copy the contents of a cell from one cell to another cell on another sheet, but I don't want all the formatting to go with it, only the value.

My code is below

  Range("H" & Target.Row).Copy Destination:=Sheets("Accepted Template").Range("P4").PasteSpecial Paste:=xlPasteValues

Any idea what I have done wrong here. VBA keeps throwing out an unexpected end error.

  • You must always specify the worksheet when you use Range. I tell about Range("H" & Target.Row).Copy – Akina Jul 30 '18 at 7:33
  • If you obtain the error and cannot understand where is the point of it in a complex operator, divide this operator to a pack of simple operators (using a lot of proper temporary variables). – Akina Jul 30 '18 at 7:36
  • Thank you for your help, but if I take off the .PasteSpecial Paste:=xlPasteValues the command actually works, all I am looking for is to have the content pasted without formatting. – Nokturnyl Jul 30 '18 at 7:40
  • Range.Copy have the only 'Destination' optional parameter. If it presents, its type is to be a Range. You want to use Range.PasteSpecial for this parameter - but Range.PasteSpecial is a method, not a property of Range type. Of course, Excel go mad... – Akina Jul 30 '18 at 8:01

You can try this Code will Copy Formatted cell value from Sheet 1 & Paste in Sheet 2 at destination cell/cells without Formatting (Only Value).

Private Sub CommandButton1_Click()
Sheets("Sheet2").Range("A3, A4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub 


  • If you want to copy Formatted range then replace line 2 in the code with this, Sheets("Sheet1").Range("A3:A10").Copy

enter image description here


  • This code can be used as Standard Module also.
  • Just to test the code purposely I've used 2 destination cells, you may use one or even many.
  • This can be done even by use of Paste Special and Value option also.
  • Thanks so much, I wouldn't have took that approach, hence why I struggled, but that worked a treat thanks again, as I am aware you have helped me several times. – Nokturnyl Jul 30 '18 at 12:27
  • @Nokturnyl, extremely happy to help you, Plz keep asking, have nice time :-) – Rajesh S Jul 31 '18 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.