I am trying to run this code in VBA:
Sub PasteContent()
' My sheet is called "PasteHere"
Dim sourceSheet As Worksheet
Set sourceSheet = ThisWorkbook.Sheets("PasteHere")
' I need it to create a new worksheet and call it "Results"
Dim destinationSheet As Worksheet
Set destinationSheet = Worksheets.Add(Before:=sourceSheet)
destinationSheet.Name = "Results"
' Paste the values of columns C, D and E from the sheet "Paste Here" into the new sheet "Results"
sourceSheet.Range("C:E").Copy destinationSheet.Range("A:C"), xlPasteValues
' Sort columns A, B and C by name in column A
destinationSheet.Range("A:C").Sort Key1:=destinationSheet.Range("A:A"), Order1:=xlAscending, Header:=xlYes
End Sub
Compile error says “wrong number of arguments or invalid property assignment” and the yellow arrow points at the “Sub” section.
I am very new to VBA so thanks a lot for your help!
ndg0029 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
The yellow arrow is pointing to Sub PasteContent() but .Copy is highlighted.
Pressing F1 with .Copy highlighted opens this help page: Range.Copy method (Excel).
Syntax
expression.Copy (Destination)
expression A variable that represents a Range object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Destination | Optional | Variant | Specifies the new range to which the specified range will be copied. If omitted, Excel copies the range to the Clipboard. |
Range only takes one parameter: Destination.
This will work:
sourceSheet.Range("C:E").Copy destinationSheet.Range("A:C")
This will copy only the values. Notice we only need the first cell in the destination.
Range("C:E").Copy Range("A1").PasteSpecial xlPasteValues
But it is best to do a direct value assignment when working with values:
destinationSheet.Range("A:C").Value = sourceSheet.Range("C:E").Value