1

Keep getting syntax error or missing ')' error. Just want to copy from one workbook to the other. Can you do a range with this formula ("A27:DE10000")? or do I need to do the dim, etc. type of formula?

Sub CopyData()

Workbooks(“COPYFROM.xlsx”).Worksheets(“ANALYSIS”).Range(“A27:DE10000”).Copy
Workbooks(“COPYTO.xlsx”).Worksheets(“ANALYSIS”).Range(“A27:DE10000”)

End Sub
1

The syntax error can be caused by a missing line continuation character (" _")

Your Sub contains 2 distinct lines (separated by carriage return?), when they should be on 1 line

And possibly, quotation marks: “ANALYSIS” vs "ANALYSIS"


.

There are different ways (and syntax) for Copy / Paste methods:

1. - On the same line:

Sheet1.Range("A1").Copy Destination:=Sheet2.Range("A1")

'Or

Sheet1.Range("A1").Copy Sheet2.Range("A1")     'Without using the named parameter

2. - On two separate lines - Worksheet.Paste (not a Range method):

Sheet1.Range("A1").Copy
Sheet2.Paste Destination:=Sheet2.Range("A1")      '.Paste method belongs to Worksheet

3. - On two separate lines - Range.PasteSpecial:

Sheet1.Range("A1").Copy
Sheet2.Range("A1").PasteSpecial Paste:=xlPasteAll '.PasteSpecial belongs to a Range Object

.

Your syntax is using version 1

To fix the error, and still be able to see both lines on the screen, without scrolling horizontally:

Option Explicit

Public Sub CopyData()

    Workbooks("COPYFROM.xlsx").Worksheets("ANALYSIS").Range("A27:DE10000").Copy _
        Workbooks("COPYTO.xlsx").Worksheets("ANALYSIS").Range("A27:DE10000")

End Sub

Or

Public Sub CopyData()

    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    Set wsSource = Workbooks("COPYFROM.xlsx").Worksheets("ANALYSIS")
    Set wsTarget = Workbooks("COPYTO.xlsx").Worksheets("ANALYSIS")

    wsSource.Range("A27:DE10000").Copy Destination:=wsTarget.Range("A27:DE10000")

End Sub

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.