Help! Trying to reference a closed workbook??

[Sharkey90]

Hi All,

I want to copy cells A1:A5 from "book1" into "book2", but if "book2" isn't open, i want the macro to open it, copy the cells into it, then save "book2". I have the below, but it doesn't work because I don't think i'm referencing the workbooks correctly? Both are saved on my desktop.

Any help is greatly appreciated.

Sub copybetweenworkbook()




Workbooks.Open Filename:="c:\book2.xlsx"


Workbooks.book1.xls.Sheets("Sheet1").Range("A1:A5").Value = Workbooks.book2.xlsx.Sheets("Sheet1").Range("A1:A5").Value


Workbooks("book2.xlsx").Activate
ActiveWorkbook.Save


End Sub

 

[Norie]

Try this.

Sub copybetweenworkbook()

    Workbooks.Open Filename:="c:\book2.xlsx"

    ActiveWorkbook.Sheets("Sheet1").Range("A1:A5").Value = ThisWorkbook.Sheets("Sheet1").Range("A1:A5").Value

    ActiveWorkbook.Close SaveChanges:=True

End Sub

 

[VBA Geek]

[FONT=comic sans ms]Workbooks("book1.xls").Sheets("Sheet1").Range("A1:A5").Value = Workbooks.("book2.xlsx").Sheets("Sheet1").Range("A1:A5").Value[/FONT]

 

[shg]

Workbooks("book1.xlsx").Worksheets("Sheet1").Range("A1:A5").Value = _
Workbooks("book2.xlsx").Worksheets("Sheet1").Range("A1:A5").Value

 

[Sharkey90]

Thanks Norie. When I run this code I get a run time error stating that book2 cannot be found, has it been moved/deleted etc. Book2 is definitely saved on my desktop and I've already tried going into book2 and entering =CELL("filename") into a cell, then using the result of this in my code but i get the same problem. Any ideas?

 

[Sharkey90]

So looks like the below works now. thanks for the help!


Sub copybetweenworkbook()


Workbooks.Open Filename:="Book2.xlsx"


ActiveWorkbook.Sheets("Sheet1").Range("A1:A5").Value = ThisWorkbook.Sheets("Sheet1").Range("A1:A5").Value


ActiveWorkbook.Close SaveChanges:=True


End Sub

 

[Norie]

Isn't that the code I posted?