Optimize excel VBA code?

[elkinm]

I am trying to optimize some macros. The biggest problem I have left is that if I want to get to a different workbook I have to use workbook.activate.

I open the new workbook NewBook.xls,
and set MyBook = ActiveWorkbook.name first to remember my workbook.

Then I use Windows("NewBook.xls").Activate and Windows(MyBook).Activate to jump between them.

This is slow and keeps flickering on the screen even if I disable screen updating. I know I can save everything I want to move/copy into variables and paste them, but that is not what I want to do.

Why can't I do: Windows(MyBook).Sheet1.Range("A1") =
Windows("NewBook.xls").Sheet1.Range("A1")

It crashes resolving the Windows("NewBook.xls")
I would think it would just be the workbook object which is directly accessible.

Also, is there a better way to switch books. Activeworbook.name relies on the name of the workbook to look it, is there a way to access the book directly?

Also, is there any way I can optimize PasteSpecial PasteLink. I do copy and paste special separately with Link:=True.

Thanks

 

[WW]

Originally posted by: elkinm
I am trying to optimize some macros. The biggest problem I have left is that if I want to get to a different workbook I have to use workbook.activate.

I open the new workbook NewBook.xls,
and set MyBook = ActiveWorkbook.name first to remember my workbook.

Then I use Windows("NewBook.xls").Activate and Windows(MyBook).Activate to jump between them.

This is slow and keeps flickering on the screen even if I disable screen updating. I know I can save everything I want to move/copy into variables and paste them, but that is not what I want to do.

Why can't I do: Windows(MyBook).Sheet1.Range("A1") =
Windows("NewBook.xls").Sheet1.Range("A1")

It crashes resolving the Windows("NewBook.xls")
I would think it would just be the workbook object which is directly accessible.

Also, is there a better way to switch books. Activeworbook.name relies on the name of the workbook to look it, is there a way to access the book directly?

Also, is there any way I can optimize PasteSpecial PasteLink. I do copy and paste special separately with Link:=True.

Thanks

I believe the "windows" collection is read only...try something like this instead:

Workbooks("NewBook.xls").Worksheets(1).Range("A1") = Workbooks("newbook2.xls").Worksheets(1).Range("a1")