VBA Code not running as intended

[emack1230]

We are using VBA to open workbooks, copy and paste into another workbook then close the workbook. When initiating the code we get sub not defined. (Changed file and sheet names for security reasons)
Thank you in advance. (FYI we are learning VBA as we go and wanting to get better)

Here is the code:

Sub OpenWorkbook()

Workbooks.Open "Copy From File"

End Sub

Sub Copy_Method()

Workbooks("Copy From File").Worksheets("Sheet Name").Range("A1:G200").Copy _
Workbooks("Destination File").Worksheets("Sheet Name").Range("A1")

End Sub


Sub CloseWorkbook()


Workbooks("Copy From File").Close SaveChanges:=True


End Sub

 

[davesexcel]

Hello,
What code errors and what line gets highlighted?

Is there a reason you have them in separate sub routines?

Possibly:

Sub OpenWorkbook()
Dim wb As Workbook, bk As Workbook

Set bk = Workbooks("Destination File.xlsx") 'assumed open
Set wb = Workbooks.Open("CopyFrom.xlsx") 'you would need the full address and name of workbook


wb.Worksheets("Sheet Name").Range("A1:G200").Copy bk.Worksheets("Sheet Name").Range("A1")


wb.Close SaveChanges:=True


End Sub

 

[emack1230]

The error message that pops up is Compile error: Sub or Function not defined. The highlighted line is the very first line Sub OpenWorkbook ()

We are both new to VBA so I wasn't sure about how many times I needed to put in Sub.

 

[davesexcel]

What happened when you tried my code?

 

[emack1230]

What happened when you tried my code?

I was on my phone didn't catch that in the reply, will apply it now and let you know. Thanks.

 

[emack1230]

It gave me Run-time error '9': Subscript out of range

Highlighted the the line - Set bk - Workbooks(Destination File.xlsx)

To note and not sure if this would matter but both of these workbooks are on Sharepoint and I opened in desktop, went to file and copied the path for each and inserted them into your code where intended.

 

[davesexcel]

Subscript out of range means it can't find something.

is Destination File.xlsx opened? There is a comment in the code that it is assumed to be opened.