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?

:)

I would copy the sheets that are required to a new workbook and fix the
values in the copies of the sheets. To do this, click the middle button
in the top right corner of the file window (Restore button), then drag
the bottom right corner up so that you can see some grey area around
the main file window.

Then select one of the sheets, hold down the CTRL key, and "drag" the
sheet tab to the grey area ("drag" means to hold down the left mouse
button and move the cursor into the grey area then release the mouse
button, then release the CTRL key). This will have copied the sheet
including all formatting into another workbook called Book1.xls by
default. You can click on the main file window (you might have to
adjust the size of the Book1 window, or you could click Window |
Arrange | Horizontal to see both windows on screen) and then select
another sheet and do a CTRL-drag on that to make a copy in Book1. Keep
doing this for as many sheets as you need.

Then with Book1 selected, choose a sheet and highlight all cells with
data/formulae in them (just click the button at the intersection of the
row and column identifiers to select all), then click <copy followed
by Edit | Paste Special | Values (check) then OK followed by <Esc.
This will have fixed all the values on the sheet, so you will have no
formulae in it. Select another sheet and repeat this procedure in each
sheet to fix all values in the workbook.

Finally, use File | Save As to give this file a different name and to
save it in an appropriate folder.

Hope this helps.

Pete

Wayne Knazek wrote:
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?

:)

Thanks, Pete. Much appreciated.

I know this option is available. What I need is automation. We have to do
this numerous times a day, from a workbook that runs on auto pilot.



"Pete_UK" wrote:

I would copy the sheets that are required to a new workbook and fix the
values in the copies of the sheets. To do this, click the middle button
in the top right corner of the file window (Restore button), then drag
the bottom right corner up so that you can see some grey area around
the main file window.

Then select one of the sheets, hold down the CTRL key, and "drag" the
sheet tab to the grey area ("drag" means to hold down the left mouse
button and move the cursor into the grey area then release the mouse
button, then release the CTRL key). This will have copied the sheet
including all formatting into another workbook called Book1.xls by
default. You can click on the main file window (you might have to
adjust the size of the Book1 window, or you could click Window |
Arrange | Horizontal to see both windows on screen) and then select
another sheet and do a CTRL-drag on that to make a copy in Book1. Keep
doing this for as many sheets as you need.

Then with Book1 selected, choose a sheet and highlight all cells with
data/formulae in them (just click the button at the intersection of the
row and column identifiers to select all), then click <copy followed
by Edit | Paste Special | Values (check) then OK followed by <Esc.
This will have fixed all the values on the sheet, so you will have no
formulae in it. Select another sheet and repeat this procedure in each
sheet to fix all values in the workbook.

Finally, use File | Save As to give this file a different name and to
save it in an appropriate folder.

Hope this helps.

Pete

Wayne Knazek wrote:
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?

:)

You said:

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.

so I don't see how this is currently done on auto pilot !!

If it is always the same 6 sheets that they want to send to the
customer, then you could record a macro while you do it once and then
just incorporate that macro into whatever code you currently have. You
could make it a bit more flexible by getting them to list the
sheetnames in a separate sheet and then invoke the "extract and fix
values" macro, which would copy whichever sheets are listed then fix
the values in those sheets.

Hope this helps.

Pete

Wayne Knazek wrote:
Thanks, Pete. Much appreciated.

I know this option is available. What I need is automation. We have to do
this numerous times a day, from a workbook that runs on auto pilot.



"Pete_UK" wrote:

I would copy the sheets that are required to a new workbook and fix the
values in the copies of the sheets. To do this, click the middle button
in the top right corner of the file window (Restore button), then drag
the bottom right corner up so that you can see some grey area around
the main file window.

Then select one of the sheets, hold down the CTRL key, and "drag" the
sheet tab to the grey area ("drag" means to hold down the left mouse
button and move the cursor into the grey area then release the mouse
button, then release the CTRL key). This will have copied the sheet
including all formatting into another workbook called Book1.xls by
default. You can click on the main file window (you might have to
adjust the size of the Book1 window, or you could click Window |
Arrange | Horizontal to see both windows on screen) and then select
another sheet and do a CTRL-drag on that to make a copy in Book1. Keep
doing this for as many sheets as you need.

Then with Book1 selected, choose a sheet and highlight all cells with
data/formulae in them (just click the button at the intersection of the
row and column identifiers to select all), then click <copy followed
by Edit | Paste Special | Values (check) then OK followed by <Esc.
This will have fixed all the values on the sheet, so you will have no
formulae in it. Select another sheet and repeat this procedure in each
sheet to fix all values in the workbook.

Finally, use File | Save As to give this file a different name and to
save it in an appropriate folder.

Hope this helps.

Pete

Wayne Knazek wrote:
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?

:)

Thanks, Pete. That gives me more direction!

"Pete_UK" wrote:

You said:

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.

so I don't see how this is currently done on auto pilot !!

If it is always the same 6 sheets that they want to send to the
customer, then you could record a macro while you do it once and then
just incorporate that macro into whatever code you currently have. You
could make it a bit more flexible by getting them to list the
sheetnames in a separate sheet and then invoke the "extract and fix
values" macro, which would copy whichever sheets are listed then fix
the values in those sheets.

Hope this helps.

Pete

Wayne Knazek wrote:
Thanks, Pete. Much appreciated.

I know this option is available. What I need is automation. We have to do
this numerous times a day, from a workbook that runs on auto pilot.



"Pete_UK" wrote:

I would copy the sheets that are required to a new workbook and fix the
values in the copies of the sheets. To do this, click the middle button
in the top right corner of the file window (Restore button), then drag
the bottom right corner up so that you can see some grey area around
the main file window.

Then select one of the sheets, hold down the CTRL key, and "drag" the
sheet tab to the grey area ("drag" means to hold down the left mouse
button and move the cursor into the grey area then release the mouse
button, then release the CTRL key). This will have copied the sheet
including all formatting into another workbook called Book1.xls by
default. You can click on the main file window (you might have to
adjust the size of the Book1 window, or you could click Window |
Arrange | Horizontal to see both windows on screen) and then select
another sheet and do a CTRL-drag on that to make a copy in Book1. Keep
doing this for as many sheets as you need.

Then with Book1 selected, choose a sheet and highlight all cells with
data/formulae in them (just click the button at the intersection of the
row and column identifiers to select all), then click <copy followed
by Edit | Paste Special | Values (check) then OK followed by <Esc.
This will have fixed all the values on the sheet, so you will have no
formulae in it. Select another sheet and repeat this procedure in each
sheet to fix all values in the workbook.

Finally, use File | Save As to give this file a different name and to
save it in an appropriate folder.

Hope this helps.

Pete

Wayne Knazek wrote:
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?

:)

Glad to be of help, Wayne.

Pete

Wayne Knazek wrote:
Thanks, Pete. That gives me more direction!

"Pete_UK" wrote:

You said:

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.

so I don't see how this is currently done on auto pilot !!

If it is always the same 6 sheets that they want to send to the
customer, then you could record a macro while you do it once and then
just incorporate that macro into whatever code you currently have. You
could make it a bit more flexible by getting them to list the
sheetnames in a separate sheet and then invoke the "extract and fix
values" macro, which would copy whichever sheets are listed then fix
the values in those sheets.

Hope this helps.

Pete

Wayne Knazek wrote:
Thanks, Pete. Much appreciated.

I know this option is available. What I need is automation. We have to do
this numerous times a day, from a workbook that runs on auto pilot.



"Pete_UK" wrote:

I would copy the sheets that are required to a new workbook and fix the
values in the copies of the sheets. To do this, click the middle button
in the top right corner of the file window (Restore button), then drag
the bottom right corner up so that you can see some grey area around
the main file window.

Then select one of the sheets, hold down the CTRL key, and "drag" the
sheet tab to the grey area ("drag" means to hold down the left mouse
button and move the cursor into the grey area then release the mouse
button, then release the CTRL key). This will have copied the sheet
including all formatting into another workbook called Book1.xls by
default. You can click on the main file window (you might have to
adjust the size of the Book1 window, or you could click Window |
Arrange | Horizontal to see both windows on screen) and then select
another sheet and do a CTRL-drag on that to make a copy in Book1. Keep
doing this for as many sheets as you need.

Then with Book1 selected, choose a sheet and highlight all cells with
data/formulae in them (just click the button at the intersection of the
row and column identifiers to select all), then click <copy followed
by Edit | Paste Special | Values (check) then OK followed by <Esc.
This will have fixed all the values on the sheet, so you will have no
formulae in it. Select another sheet and repeat this procedure in each
sheet to fix all values in the workbook.

Finally, use File | Save As to give this file a different name and to
save it in an appropriate folder.

Hope this helps.

Pete

Wayne Knazek wrote:
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?

:)