VBA Code to open sourc workbook, copy columns, past to target workbook, and close source.

Still pretty new to VBA, I have tried many different methods with no success. I am looking to click a button, with a macro assigned that will use Application.GetOpenFileName to choose a source file, open it, copy some columns (F for example), paste them to the current (target) workbook, and then close the source workbook. Does anyone have any suggestions? I am getting runtime error 9. Thanks. Here is my current code:



Sub Macro2()

'Assign variable name to Target workbook
Var1 = ActiveWorkbook.Name
'Assign variable name to Target range
Var1R = "H1"

'Open Source WorkBook
sPath = Application.GetOpenFilename
Workbooks.Open Filename:=sPath

'Assign variable name to Source workbook
Var2 = ActiveWorkbook.Name
Var2R = "WORKSHEET-1"

'Copy from Source to Target
Sheets(Var2R).Columns("F").EntireColumn.Copy _
Destination:=Workbooks(Var1).Sheets("Sheet1").Range(Var1R)

'Close Source WorkBook wo/Save
Workbooks(Var2).Close False


End Sub

Try this,

Please Login or Register  to view this content.