Copy from 1 book to another

[Khalil Handal]

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1 and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Nick Hodge]

Khalil

The best way is to open both books and just click in the cell of the other
book after typing = in cell W7. All will then be looked after for you

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Pete_UK]

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

 

[Khalil Handal]

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1 and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Gord Dibben]

Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse down to Copy/paste/merge examples.


Gord Dibben MS Excel MVP

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1 and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Khalil Handal]

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse down to Copy/paste/merge examples.


Gord Dibben MS Excel MVP

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Gord Dibben]

Your choice for copying from a closed workbook.

I may have misinterpreted what you are wanting to do and that is type a linking
formula in a target workbook without the source workbook opening.

I have no problem typing in Book1.xls Sheet1

=[Book2.xls]Sheet2!B3

without Excel looking for Book2.xls or throwing an error message.


Gord

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse down to Copy/paste/merge examples.


Gord Dibben MS Excel MVP

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?


The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Pete_UK]

Gord,

you must have had a "Book2.xls" in the current active folder for this
to work without error - if there isn't a file with that name then
Excel will present you with the File Not Found dialogue so that you
can specify the folder where the file is located. The OP may have
opened the file Book1 by double-clicking it, and so Excel will not
have set the active folder to the folder where it (and Book2) resides.

Pete

Your choice for copying from a closed workbook.

I may have misinterpreted what you are wanting to do and that is type a linking
formula in a target workbook without the source workbook opening.

I have no problem typing in Book1.xls Sheet1

=[Book2.xls]Sheet2!B3

without Excel looking for Book2.xls or throwing an error message.

Gord

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

Khalil
With VBA you can copy from a closed workbook.
Couple of methods at Ron de Bruin's site.
http://www.rondebruin.nl/tips.htm
Browse down to Copy/paste/merge examples.
Gord Dibben MS Excel MVP
Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?
The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:
=[Book2.xls]Sheet2!B3
However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.
Hope this helps.
Pete
Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?
thanks- Hide quoted text -

- Show quoted text -

 

[Khalil Handal]

The dialog box that appera has a title about "update values"????
=Book2.xls]Sheet2!B3
when pressing enter key the update value message popped on the screen.

Your choice for copying from a closed workbook.

I may have misinterpreted what you are wanting to do and that is type a
linking
formula in a target workbook without the source workbook opening.

I have no problem typing in Book1.xls Sheet1

=[Book2.xls]Sheet2!B3

without Excel looking for Book2.xls or throwing an error message.


Gord

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse down to Copy/paste/merge examples.


Gord Dibben MS Excel MVP

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location
of
the second book (to copy from).
Is there another way? Using VBA or what ever?


The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Pete

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks

 

[Gord Dibben]

Pete

You are correct in your assumption................tested with Book2.xls in
current active folder.

Quality control around here just isn't up to snuff<g>

Thanks for the info.


Gord

Gord,

you must have had a "Book2.xls" in the current active folder for this
to work without error - if there isn't a file with that name then
Excel will present you with the File Not Found dialogue so that you
can specify the folder where the file is located. The OP may have
opened the file Book1 by double-clicking it, and so Excel will not
have set the active folder to the folder where it (and Book2) resides.

Pete

Your choice for copying from a closed workbook.

I may have misinterpreted what you are wanting to do and that is type a linking
formula in a target workbook without the source workbook opening.

I have no problem typing in Book1.xls Sheet1

=[Book2.xls]Sheet2!B3

without Excel looking for Book2.xls or throwing an error message.

Gord

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

Browse down to Copy/paste/merge examples.

Gord Dibben MS Excel MVP

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and location of
the second book (to copy from).
Is there another way? Using VBA or what ever?

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen. Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks- Hide quoted text -

- Show quoted text -

 

[Khalil Handal]

The formula works well if the path (folder) in which the file exists is
included:
='d:\data\[book2.xls]sheet1!B3

Khalil

Gord,

you must have had a "Book2.xls" in the current active folder for this
to work without error - if there isn't a file with that name then
Excel will present you with the File Not Found dialogue so that you
can specify the folder where the file is located. The OP may have
opened the file Book1 by double-clicking it, and so Excel will not
have set the active folder to the folder where it (and Book2) resides.

Pete

Your choice for copying from a closed workbook.

I may have misinterpreted what you are wanting to do and that is type a
linking
formula in a target workbook without the source workbook opening.

I have no problem typing in Book1.xls Sheet1

=[Book2.xls]Sheet2!B3

without Excel looking for Book2.xls or throwing an error message.

Gord

Hi,
I choosed the following:

Copy/Paste/Merge examples
Merge cells or sheet from all or some workbooks in a folder
Copy a range from closed workbooks (ADO)
Copy range or sheet to all workbooks in a folder

Are these ok?
I will see the articles.
Thanks

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Khalil

With VBA you can copy from a closed workbook.

Couple of methods at Ron de Bruin's site.

Browse down to Copy/paste/merge examples.

Gord Dibben MS Excel MVP

@stthom.edu>
wrote:

Is there a way without opening the first book?
I tried to type the formuls:
=[Book2.xls]Sheet2!B3
And has a windows asking for a file name which is the name and
location of
the second book (to copy from).
Is there another way? Using VBA or what ever?

The easiest way to derive the formula is to open Book2 as well as
Book1 and arrange the windows so that you can see both on screen.
Then
with the cursor in W7 of Sheet3 in Book1, type the equals sign then
click on the window for Book2, select Sheet2 then click in cell B3
then press <Enter>. In W7 you should see the formula:

=[Book2.xls]Sheet2!B3

However, if you now close Book2, then the part of the formula within
square brackets will expand to include the full path, so that you
don't need to type this all yourself.

Hope this helps.

Hi,
I am at the address W7 in Bok1 in Sheet3.
I need to copy the contents of Cell B3 in Sheet2 in Book2 where
Book1
and
Book2 are in the same folder.
What formula is needed to be typed in cell W7?

thanks- Hide quoted text -

- Show quoted text -

 

[Indrani]

copy .xls sheets form one book to another

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com