copy values from 11 books to 1 sheet

[Khalil Handal]

Hi,
I am in Book2 in sheet1 cell D4. I need to copy a value from another place.

What formula do I need to copy cell K67 from the CLOSED workbook named
"Book1" from "sheet2" where sheet2 is protected with a password
TO
Book2 sheet1 in cell D4.

Actually I need to copy 11 different workbooks 4 values from each book from
the same sheet.

 

[Pete_UK]

It is MUCH easier to open Book1, even temporarily, and have two
windows visible on screen. Then you type = in cell D4 of Book2, click
over to the Book1 window, select the Sheet2 tab and then click on the
cell you want to copy from (K67) (actually, you are linking to it, but
it is effectively the same). Then press <Enter>, and Excel will have
taken care of the syntax rules. Close Book1, and you will see the
formula in D4 - essentially made up of the full path, filename,
sheetname and cell reference. You can repeat this procedure for other
files, as required.

Hope this helps.

Pete

 

[Khalil Handal]

Hi,
Thanks .
I tried it and worked well. I wasn't shure at the begining wether the book1
MUST be opened or not bt it turned out that it is not necessary.

If I copy all the workbooks to another drive and another folder I THINK it
will ask me to update the links. Is this true?? (I havn't tried it yet!)

Thanks again.
Khalil

 

[Pete_UK]

It will always ask you if you want to update external links whenever
you open the file. As for moving the files to another folder, how will
Excel know that you have done this? It will look in the path (from
your formulae) for the file and if it is not there it will tell you
"File not found", so it will not be able to update the links (i.e. the
values of the links, not the links themselves). If you are planning to
move the files then do that first, before setting up the link
formulae.

Hope this helps.

Pete

 

[Khalil Handal]

Thanks for the note Pete.
I remember reading something in one of the postings about using VBA code
that TELLS Excel to look in the CURRENT PATH.
I couldn't find it! Do you know how to do it? Is there a site that tells
more about it?

 

[Pete_UK]

I don't recollect that posting, and I don't do enough in VBA to be
able to advise you on it.

Sorry,

Pete

 

[Khalil Handal]

Thanks Any Way.
I will do a posting for it, maybe I can get help about it

 

[Pete_UK]

You're welcome.

Pete

Thanks Any Way.
I will do a posting for it, maybe I can get help about it






- Show quoted text -