New to VBA: Compile Error Syntax Error

[j9040]

Attached below is the code I am trying to run. My goal is to pull data from one sheet in one workbook and paste it into another sheet in another workbook seamlessly for a large amount of data. I have an error returning in line 1, the "Sub" line, giving me a "Compile Error: Syntax Error". How do I prevent this from happening? Thank you!




Sub Copy_Method()
Workbooks("Data.xlsm").Worksheets("Company").Range"D7:14".Copy _
Workbooks("Other Data").Worksheets("Company Calcs").Range"AI9:AI16"
End Sub

[Paul_Hossler]

Welcome to the forum

Take a couple of minutes and read the FAQ at the link in my sig

I'm guessing that you're missing parentheses around the Range addresses




Sub Copy_Method()
Workbooks("Data.xlsm").Worksheets("Company").Range("D7:14").Copy _
Workbooks("Other Data").Worksheets("Company Calcs").Range("AI9:AI16")
End Sub

[j9040]

Thank you for the assistance! Now, upon running my code, I am receiving Run-time error '40036': Application-defined or object-defined error. I tried to do research on how to handle this but came up short. It is occurring with the simple code I have written thus far. Any advice on how to handle this error? The help button and Microsoft support page did not help.

[arnelgp]

i was thinking you need to change "windows" when copying:


Sub Copy_Method()
Windows("Data.xlsm").Activate
Worksheets("Company").Select
Range("D7:14").Copy
Windows("Other Data").Activate
Worksheets("Company Calcs").Select
Range("AI9:AI16").Select
ActiveSheet.Paste
End Sub

[SamT]

Both errors:
Range("D7:D14")
Range("AI9:AI16")

However

Workbooks("Data.xlsm").Worksheets("Company").Range("D7:D14").Copy _
Workbooks("Other Data").Worksheets("Company Calcs").Range("AI9")
Should work

In the interests of robustness and mutability, I prefer

Sub Copy_Method()
Dim RngSrc as Range, RngDest As Range

Set RngSrc = Workbooks("Data.xlsm").Worksheets("Company").Range("D7:D14")
Set RngDest = Workbooks("Other Data").Worksheets("Company Calcs").Range("AI9:AI16")

RngSrc.Copy RngDest
End Sub

You won't see much benefit with your little one line sub, but when your code starts to need several screens and modules, it will really make a difference when you need to Reuse and Refactor.


Sub1
Blah blah
Copy_Method(Range1, Range2)
End Sub

Sub2
Blah blah
Copy_Method(RngA, RngB)
End Sub

Sub3
BlahBlah
Copy_Method(ThisRange, ThatRange)
End Sub

'__________________________________________________
Sub Copy_Method(ByVal RngSrc As Range, ByVal RngDest As Range)
'A well developed Sub Routine that can handle anything and every thing thrown at it.
'It seemed that every time a new piece of main code was written it exposed a new _
error that could best be handled herein.

'Some error Correcting here

'On Error Go To...
RngSrc.Copy RngDest

'Error reporting here
'Error handling here
End Sub

[snb]

Keep it simple when starting in VBA.


Sub M_snb()
Workbooks("Data.xlsm").sheets("Company").Range("D7:D14").copy Workbooks("Other Data.xlsm").sheets("Company Calcs").Range("AI9")
End Sub

NB. Both workbooks have to be 'loaded'/'opened' in Excel.