excelvba

VBA Paste in new sheet macro - getting "wrong number of arguments or invalid property assignment"

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!

Solution

  • The yellow arrow is pointing to Sub PasteContent() but .Copy is highlighted.

    enter image description here

    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