"Object Required" Error

[Rutger54]

I need to copy from anohter workbook on my I:/ drive and paste into "Sheet 1". I have the cde below but get an "Object Required Error. Please help?? Thanks in advance


Sub Macro2()


'Assign variable name to Target workbook
Var1 = "ActiveWorkbook.Name"
'Assign variable name to Target range
Var1R = "A1:AD23"

'Open Source WorkBook
Application.Workbooks.Open ("I:\Currency Futures\JPMFI - Currency Futures\PWD BALANCE BY ACCT\PWD_BALANCE_BY_ACCOUNT.csv")

'Assign variable name to Source workbook
Var2 = "PWD_BALANCE_BY_ACCOUNT.csv"
Var2R = "PWD_Balance_By_Account"

'Copy from Source to Target

Sheets(Var2R).Select.Copy
Destination: Workbooks(Var1).Sheets("Sheet1").Paste
'Close Source WorkBook wo/Save
Workbooks(Var2).Close False


End Sub

 

[Gary McMaster]

One problem is the following line. ActiveWorkbook.Name should not be in quotes.

Var1 = "ActiveWorkbook.Name"

Gary

 

[Rutger54]

Figured this out! Here's what I did for anyone interested!

Sub Macro2()

'Assign variable name to Target workbook
Var1 = ActiveWorkbook.Name
'Assign variable name to Target range
Var1R = "A:AD"

'Open Source WorkBook
Application.Workbooks.Open ("I:\Currency Futures\JPMFI - Currency Futures\PWD BALANCE BY ACCT\PWD_BALANCE_BY_ACCOUNT.csv")

'Assign variable name to Source workbook
Var2 = ActiveWorkbook.Name
Var2R = "PWD_Balance_By_Account"

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

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