Activating a workbook

Giganews Newsgroups
Subject: Activating a workbook
Posted by:  Jeff Wright (jeffwrig…@cox.net)
Date: Sat, 1 May 2004

I am working on a macro which opens a workbook ("Info"), while another one
("Original") is already active. My macro successfully opens "Info", and
copies data from it to "Original". However, when the macro attempts to
reactivate "Info" in order to copy additional data, the macro halts with a
"subscript our of range" error. Below is the code:

'  This routine asks for a file name and opens it

    Answer = InputBox(Prompt:="Enter the File Name")  '(for example, user
will type in "Info")
    RetrievedFile = "C:\My Documents\Jeff's Documents\Excel Files\" + Answer
+ ".xls"
    Workbooks.Open Filename:=RetrievedFile
    Range("C1").Select
    Selection.Copy
    Windows("Original").Activate
    Range("I1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Open
    Workbooks(RetrievedFile).Activate

    The last line above halts the macro and results in a "Subscript out of
range" error.

Help!!!

Replies