Automatically pasting worksheet data to new worksheet with formulas
 

I have written a worksheet that populates data from a closed workbook
and performs calculations on the data form the closed workbook. I am
not highly skilled with VB and I try to write workbooks with as little
macros as I can.

I populate the data from a closed workbook with a modified function of
INDIRECT called INDIRECT.EXT. That is how I can read from a closed
workbook.

The workbook pulls up data from my cash register for the entire month.
Because of this it takes several minutes (about 3 to 5) to calculate
the workbook. Not that big of a deal but the workbook also has cells
that I need to manually input data into. Therefore, every time I enter
data into one cell the entire workbook recalculate and subsequently,
every manual entry takes about 3-5 minuets. So I set calculation to
‘manual’ but that is not good because if I enter a mistake
I do not catch it until I recalculate again.

Now for the questions:
1- Would it be better to lookup the data from closed workbooks via VB?
Meaning would the recalculation time return to normal (a slit second.)

2- I don’t mind the 3-5 minute calculation as long as I only have
to do it once. So is there a way I can macro or preferably use a
FUNCTION call that copies all the cell data in the worksheet over to a
new workbook/worksheet by just pasting the data and not the formulas?
Also at the same time I want to create the worksheet name via data
located in the worksheet. This way I can manually enter the date I need
to against the data copied over from the closed workbook with the heavy
calculation load put on by the INDIRECT.EXT function.

3- I have run the marco recorder and selected all cells, created a new
workbook, special paste for values only, and named the file. How do I
program the macro to browse for the workbook and create a new worksheet
and asks fo the name?

I am hapyy to attach the workbook and marco is needed.


--
COntactJason
------------------------------------------------------------------------
COntactJason's Profile: http://www.excelforum.com/member.php...o&userid=26143
View this thread: http://www.excelforum.com/showthread...hreadid=394729