Excellent! Thanks for the code too.


"JLatham" wrote:

Monica,
You'll need to tell your macro which Window to work in. After opening the
second workbook, it will actually be the active window. Each window is
referenced by the name of the workbook opened in it. I think just recording
a short macro or two to open a second book and do some copying and pasting
will show you what you need to know about how to reference sheets and cells
in each one. You can assign workbook names, sheet names, etc to variables
and use them in your references. I often use variable names like sourceBook,
destBook, sourceSheet, destSheet to help with that; setting them up in the
code.

Here is a little more code showing actual going back and forth between two
books - although you can do most of this by referencing ranges and using full
reference to which book/sheet/range you want which can speed things up
greatly. If you do the actual jumping around between books and on sheets,
for heaven's sake use
Application.ScreenUpdating = False at the beginning of such operations
followed by Application.ScreenUpdating = True at the end. Again, a huge time
saver.

Windows("second.xls").Activate
Sheets("Sheet 1").Select
Range("B7:H17").Select
Selection.Copy
Windows("first.xls").Activate
ActiveSheet.Paste

To 'physically' jump between workbooks, the code goes like this
Windows("Book1").Activate



"monica" wrote:

I currently have a file (first.xls) open and use the following code to open a
second (second.xls):

sEventName = "Initialization"
gsWorkPath = "c:\documents and settings\me\my documents\second.xls"
Application.Workbooks.Open Filename:=gsWorkPath

This part works. My problem is now I need to read/copy the contents of a
tab in second.xls into first.xls. But now I just get "subscript out of range
error" everytime I try to work with either file.

For example, the line: ActSheet = "sheet1" gives the error
and so does: Worksheets("sheet1").Activate

How do I refer to a sheet or a workbook now? Worked fine before opening a
second one.

Thanks in advance. Sample code is MUCH appreciated.