Excel 2000. How do I copy data a worksheet from another workbook and paste
it in a worksheet on the current workbook that i'm opening. I would like
this all done without the user knowing about the copy when the user opens the
workbook. I'm using countif to obtain totals for a survey. Countif will not
link to another workbook unless that workbook is open. So, when my
spreadsheet opens I have a macro that deletes a worksheet in my totals
workbook and then the macro copies a worksheet from a seperate workbook and
places it in my totals workbook. The problem with this is that formulas
referencing the worksheet that is deleted are lost when the worksheet is
deleted.

Maybe you could replace your =countif() formulas with a different one?

I can replace this formula:

=COUNTIF('C:\My Documents\excel\[Book2.xls]Book2'!$A$1:$A$25,"asdf")
with
=SUMPRODUCT(--('C:\My Documents\excel\[Book2.xls]Book2'!$A$1:$A$25="asdf"))

(One difference--I can't use the whole column with =sumproduct().)



Jet wrote:

Excel 2000. How do I copy data a worksheet from another workbook and paste
it in a worksheet on the current workbook that i'm opening. I would like
this all done without the user knowing about the copy when the user opens the
workbook. I'm using countif to obtain totals for a survey. Countif will not
link to another workbook unless that workbook is open. So, when my
spreadsheet opens I have a macro that deletes a worksheet in my totals
workbook and then the macro copies a worksheet from a seperate workbook and
places it in my totals workbook. The problem with this is that formulas
referencing the worksheet that is deleted are lost when the worksheet is
deleted.

--

Dave Peterson