One workbook to another . . .

W

Wayne Knazek

I'm not looking for volumes of info on this one. (Um, that doesn't mean I
wouldn't appreciate it, though! LOL) I just need a jump start so I can
figure out what to do to get started.

I have a workbook with about 50 sheets. 4 of the sheets get data, dumped
from a program. That data is carried to other sheets as needed.

These "other" sheets basically just have a formula in each cell, so data is
pulled from the main sheet it's "connected" to.

Now that the workbook is working great, I FINALLY get feedback from our
Engineering dept. They have 2 concerns . . .

The dilemma . . .

1). If they send the finished workbook to the customer, the customer can
view any/all formulae in the cells. They don't want the customer to see any
formulae.

2). The size of the file is fairly large. Because not only are the final
Engineering reports part of the workbook, but the sheets that do the "math",
and our internal reports are all a part of the same workbook.

So . . . they want to delete all but the 5 or 6 sheets with "their" data.
But if they do . . . the source sheets supplying the data go, and their
sheets end up with nothing in them.

The solution(s) . . .

I know I can hide formulae. Haven't yet gone in and tried to figure out
how. But I know I can do that. So that part shouldn't be an issue. But . .
..

They can't delete the source of the data! So what I'd like to do is . . .

Make another workbook with just the Engineering portion. Then, once all the
calculations are done, and the data is in the appropriate Engineering sheets,
have the data go from the Engineering sheets in the main workbook . . . to
the copy of the workbook. But just the values . . . not the formulae.

As in, EX: Sheet D2 gets data from one program, does some calculations, and
puts the results into a column in that sheet. The data from the colum with
the calculated results is sent to sheet E2. How? Sheet E2 has a formula in
each cell pulling that data over. You know . . . if something is in this
cell on that sheet, put it here.

Now comes workbook 2. I'd like sheet E2 of new workbook to receive the
values of main E2. Without having a formula in every cell in the new E2.

So when it's done, Engineering can send the new workbook to the customer,
and still have the "working" workbook for reference.

My best guess is . . . have all the cells in the heading section of new
workbook use the formula to pull identical info (text) over, so the heading
is done identical to main workbook. Use the "hide formula" function for the
heading. Then somehow have all columns in main workbook sent over to new
workbook, without forumlae in each cell of new workbook. (Ugghhh!)

Does that make sense?

:)
 
B

Bernard Liengme

Have you thought of copying the summary page and then , in another workbook,
use Edit | Paste Special -> Values?
best wishes
 
W

Wayne Knazek

That would be a last resort only. My example is WAY over simplified. It
would require hours of work to get'r done that way.

We need to automate totally. The workbook is used for 100's of layouts.
Each time our programs run, the program opens the master workbook. Dumps the
data. Then we save it as a new file name, keeping original workbook in tact.

This could be done numerous times a day.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top