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

 

[VoG]

Try

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").Value = Workbooks("Book1Template.xlsx").Sheets("Graphings").Range("B3").Value

End Sub

 

[Richard Schollar]

You have two undefined variables in this line:

Actual_Participation_02_2011.Sheet(1).Range("A2:A1000").Value = Book1Template.xlsx.Sheet("Graphings").Range("B3").Value

You need to either set variables to be these values, or refer to the workbooks using the Workbooks() object:

Workbooks("Actual_Participation_02_2011.xlsx").Sheet(1).Range("A2:A1000").Value = Workbooks("Book1Template.xlsx").Sheet("Graphings").Range("B3").Value

 

[Legacy 93538]

Hi

Thanks for replying. I ahve tried both ways and it came up with another error saying "Object Doesn't supportthis property or method"

 

[VoG]

Did you change Sheet to Sheets (twice).

 

[Legacy 93538]

No i forgot....i ahve not changed them and i ran it again andi got another error saying subscript out of range

 

[VoG]

Check carefully the spelling of the Graphings sheet - check for leading or trailing spaces.

 

[Legacy 93538]

Done that but it is still saying subscript out of range

 

[VoG]

Click Debug. Which line of code is highlighted?

 

[Legacy 93538]

Ok...it hightlights this line

 Workbooks("Actual_Participation_02_2011.xlsx").Sheets(1).Range("A2:A1000").Value = Workbooks("Book1Template.xlsx").Sheets("Graphing").Range("B3").Value