Writing code to switch between workbooks?

[JoetheSousChef]

I have three open workbooks. I want to cut and paste different pieces of data from Book1 to Book3. The macro recorder writes

 ActiveWindow.ActivateNext

as the code to advance to the next workbook in response to my Ctrl-Tab sequence on the keyboard.

That's fine but it requires that the workbooks be opened in the same sequence each time I run the macro.

I thought I could use Activate to specify the workbook I wanted to be active in; as in

Workbooks("BOOK1").Activate

which I copied directly from the example in VBA Help. This line, however, stops the program with a report of "run time error 9" "subscript out of range".

Would appreciate any help for a novice programmer on two points.
.Are there other (simple) ways to specify
which workbook is the active workbook to copy from/
paste to?

.Why does the second line of code stop the macro
running?

Thanks in advance for your time.

 

[Loomah]

Chef
A couple of thoughts for you

>"which I copied directly from the example in VBA Help"
Do you have a workbook called "BOOK1"? The code would normally be:-

Workbooks("NameOfWorkbookToActivate").Activate

You could copy and paste without "activating" the books, as long as they're open
ie

Workbooks("SourceBook").Worksheets("Source").Range("A1:B2").Copy _
    Destination:=Workbooks("DestinationBook").Worksheets("Destination").Range("A1")

If there's a lot of switching backward and forwards I would assign the workbooks to Object Variables. If nothing else this cuts down on the typing needed!!

eg

Sub a()
Dim wbSource As Workbook, wbDest As Workbook
Set wbSource = Workbooks("SourceBook")
Set wbDest = Workbooks("DestinationBook")

wbSource.Worksheets("Source").Range("A1:B2").Copy _
    Destination:=wbDest.Worksheets("Destination").Range("A1")

'some other stuff you could do!!
wbSource.Activate
wbSource.Close

Set wbSource = Nothing
Set wbDest = Nothing
End Sub

You could also assign the variables as you open the workbooks
ie set wbSource = workbooks.open filename

Hope this helps some!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[SkipVought]

Hi,

Loomah has given you some excellent advice.

I'll just add one of my FAQ's, emphasizing that in MOST cases you do not need to use the Activate/Select methods to accomplish your tasks

faq707-4105 How Can I Make My Code Run Faster?



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com