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.

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.

Generally, with two workbooks open, you need to specify which one to do
something with. There are several ways to do so. The following 2
commands
(4 lines in Book1.xls) do the same thing.

Workbooks("Book2.xls").Sheets("Sheet1").Range("A2: B3").Copy _
Destination:=Workbooks("Book1.xls").Sheets("Sheet1 ").Range("A5")
Workbooks("Book2.xls").Sheets("Sheet1").Range("A2: B3").Copy _
Destination:=ThisWorkbook.Sheets("Sheet1").Range(" A5")
Application.CutCopyMode = False

Hth,
Merjet

Thanks! for demonstrating exactly what I meant when I wrote
"...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. "
above.

"merjet" wrote:

Generally, with two workbooks open, you need to specify which one to do
something with. There are several ways to do so. The following 2
commands
(4 lines in Book1.xls) do the same thing.

Workbooks("Book2.xls").Sheets("Sheet1").Range("A2: B3").Copy _
Destination:=Workbooks("Book1.xls").Sheets("Sheet1 ").Range("A5")
Workbooks("Book2.xls").Sheets("Sheet1").Range("A2: B3").Copy _
Destination:=ThisWorkbook.Sheets("Sheet1").Range(" A5")
Application.CutCopyMode = False

Hth,
Merjet

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.

Look at merjet's offering - shows how to do it without actually jumping back
and forth between the windows/sheets. Much faster to do it that way. The
screen updating and refreshing in doing actual 'physical' swapping of stuff
on screen is an absolute killer. I've seen processes improve by a factor of
30 or more just by doing it all in memory or simply using the
Application.Screenupdating=False code.

"monica" wrote:

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.