Copying from data from another worksheet to exisiting.

[jnair]

Hi-
Could someone tell me the way to copy a closed worksheet to the
existing one.

I have a macro in a template, and it gets kicked off every morning. I
want to get the data from a another worksheet when it gets kicked off
and then do the manipulation with that data.Could someone help!I would
really appreciate it.

Thanks
jnair

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

If the range doesn't change, maybe you could just use links:

Open both workbooks.
in the "receiving" cell, type =
use the mouse to point at the other cell.
You'll end up with a formula like:

=[book1.xls]Sheet1!$E$7

You may want to use this
=if([book1.xls]Sheet1!$E$7="","",[book1.xls]Sheet1!$E$7)
to avoid returning 0 from empty cells.

Now close that other workbook and you'll see your formulas adjust to something
like:

=IF('C:\My Documents\excel\[book1.xls]Sheet1'!$E$7="","",
'C:\My Documents\excel\[book1.xls]Sheet1'!$E$7)
(all one cell)

When you open the workbooks with links, just answer "Yes" to the update links
question.

If the range can change, I'd just open the other workbook and copy and paste
(using a macro tweaked after recording).

Ron de Bruin has some code that uses ADO to retrieve values from a closed
workbook:
http://www.rondebruin.nl/ado.htm