Copying Worksheet

[Guest]

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]

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().)