Compile Error

[Legacy 93538]

Hi

I have written a macro in VBA which should open two documents and copies data from A2:A1000 to B3:B2000

 Option Explicit
Sub AverageGraph()
 
Application.Workbooks.Open ("C:\Documents and Settings\SeymourJ\Desktop\Book1Template.xlsx")
 
Application.Workbooks.Open ("C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_2011.xls")
 
Actual_Participation_02_2011.Sheet(1).Range("A2:A1000").Value = Book1Template.xlsx.Sheet("Graphings").Range("B3").Value

End Sub

However every time i run it i get a Compile Error saying Variable not defined. Can anyone tell me why i am getting it?

Thanks

JessicaSeymour

 

[Legacy 93538]

I am using excel 2010...does that help?

 

[VoG]

The error means that it isn't finding something - probably a sheet. Workbooks("Actual_Participation_02_2011.xls").Sheets(1) must exist so the problem must be with Workbooks("Book1Template.xlsx").Sheets("Graphings")

 

[Legacy 93538]

Not sure should find it as both the workbooks are open.....What i am trying to do is open the two workbooks and copy the range from Actual_Participation_02_2011.xls range A2:A1000 and then paste it into Book1Template.xlsx in the sheet graphing into B3:B10001 I tried writing VBA to copy and paste seperately but i couldn't get to work so i tried the line above which also doesn't work.

 

[VoG]

Does this work?

Sub AverageGraph()
 
Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Book1Template.xlsx"
 
Application.Workbooks.Open "C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_2011.xls"
 
Workbooks("Actual_Participation_02_2011.xls").Sheets(1).Range("A2:A1000").Copy Destination:=Workbooks("Book1Template.xlsx").Sheets("Graphings").Range("B3")

End Sub

 

[Legacy 93538]

No its still saying subscript out of range

 

[VoG]

Well, I'm sorry but a sheet (probably Graphings) must be named wrongly (Excel can't find it).

 

[RoryA]

Then you can't have a sheet called Graphings, I don't think. Just to test:

Sub AverageGraph()
Dim wbk1 as workbook, wbk2 as workbook
set wbk1 = Application.Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\Book1Template.xlsx")
set wbk2 = Application.Workbooks.Open("C:\Documents and Settings\SeymourJ\Desktop\Actual_Participation_02_2011.xls")
wbk2.Sheets(1).Range("A2:A1000").Copy Destination:=wbk1.Sheets("Graphings").Range("B3")
End Sub

 

[Legacy 93538]

Hi

I have figurerd it out book1template is wrong its book2 not book1.

Thank you for all your help....you have been very helpful