Using 2 open workbooks

[matt_steer]

i am getting subscript out of range error when i am using th
Windows(Filename).Activate command to switch from one open work book t
another....

Is this the correct way? or should i be using another command...?

All i want to do is the following...

Switch to WKBK2
Copy Cells
Switch to WKBK1
Paste Cells
Switch to WKBK2
Copy more cells
Switch to WKBK1
Paster more cells

:

 

[Pete McCosh]

Matt,

to switch between workbooks, I would use:

Workbooks("Book 2").activate

However, for the purposes of your code, there is no need
to switch between them at all, and it'll make your macro
run a whole lot slower if you use it. If I assume that
WKBK1 in your example is the active workbook when you
start, then this code (written on one line, not two) will
copy data from a specified location on the second workbook
and paste it into th efirst without having to select,
activate or switch between anything:

Workbooks("Book2").Sheets("Sheet1").Range("B4:G5").Copy
destination:= ThisWorkbook.Sheets("Sheet2").range("D4")

Cheers, Pete.

 

[Jean-Yves]

Hi Matt,

The macro recorder uses window ("workbook name").
Use :
Workbooks("your workbook name 1").activate
Copy
Workbooks("your workbook name 2").activate
Paste
This assumes that the corrcet worksheet is also active !!

Or in one line, and avoid screen flashing :
Workbooks("your workbook name 1").Worksheets("your sheet
name").range("A1:B2).copy Workbooks("your workbook name 2").Worksheets("your
sheet name").range("A1:B2)

Shorter is possible if you assign certain objects to mvariables,

Regards,

Jean-Yves

 

[Kris]

This is because you are probably supplying the full file
path in your Windows(Filename).Activate command.

Instead of using Windows("c:\myfilename.xls").Activate,
you need to use Windows("myfilename.xls").Activate.

If you just have the full path, you can use InstrRev
(Win2K or later I think) to search for the last "\" to
parse off the full file path. If you are using an older
version of excel, you have to loop through the file name
until you find the last "\" like:

start_pos = 1
found_pos = InStr(start_pos, filename, "\")
Do While (found_pos <> 0)
start_pos = found_pos + 1
found_pos = InStr(start_pos, filename, "\")
Loop

worksheetname = Right(filename, Len(filename) - start_pos
+ 1)

 

[matt_steer]

still get subscript out of range !!!

I am using the following syntax...

Windows(wkbk1).Activate
Windows(wkbk2).Activate

where wkbk1 is a variable got from user selection

and wkbk2 is a variable assigned to the workbook from where the macr
is running

suggestions???

 

[matt_steer]

Done it.....

It was the path to the file i was using in the expression....

: