How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.

On a copy of your original workbook.

Select all sheets by right-click on a sheet tab and "select all sheets".

CTRL + a(twice in 2003) to select all cells on all sheets.

Copy then in place.........Paste SpecialValuesOKEsc.

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP


On Sun, 28 Oct 2007 19:51:01 -0700, skysusan
wrote:

How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.

Thanks for your reply.

However, by copying values, it will lose all the color and format from the
original worksheet. I would like to save it as another Excel file, with the
format, color, data value, without the formula that import the data from
external source.

Is there a feature like that?

skysusan

"Gord Dibben" wrote:

On a copy of your original workbook.

Select all sheets by right-click on a sheet tab and "select all sheets".

CTRL + a(twice in 2003) to select all cells on all sheets.

Copy then in place.........Paste SpecialValuesOKEsc.

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP


On Sun, 28 Oct 2007 19:51:01 -0700, skysusan
wrote:

How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.

Did you try it?

I don't lose formatting when I paste in place.

If you do, you could copy again and paste specialformats.


Gord

On Sun, 28 Oct 2007 20:49:01 -0700, skysusan
wrote:

Thanks for your reply.

However, by copying values, it will lose all the color and format from the
original worksheet. I would like to save it as another Excel file, with the
format, color, data value, without the formula that import the data from
external source.

Is there a feature like that?

skysusan

"Gord Dibben" wrote:

On a copy of your original workbook.

Select all sheets by right-click on a sheet tab and "select all sheets".

CTRL + a(twice in 2003) to select all cells on all sheets.

Copy then in place.........Paste SpecialValuesOKEsc.

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP


On Sun, 28 Oct 2007 19:51:01 -0700, skysusan
wrote:

How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.

Yes, I can copy format and copy values into another Excel worksheet.

Just hoping a "SAVE AS" feature will do a similar job.

Thanks a lot.
skysusan

"Gord Dibben" wrote:

Did you try it?

I don't lose formatting when I paste in place.

If you do, you could copy again and paste specialformats.


Gord

On Sun, 28 Oct 2007 20:49:01 -0700, skysusan
wrote:

Thanks for your reply.

However, by copying values, it will lose all the color and format from the
original worksheet. I would like to save it as another Excel file, with the
format, color, data value, without the formula that import the data from
external source.

Is there a feature like that?

skysusan

"Gord Dibben" wrote:

On a copy of your original workbook.

Select all sheets by right-click on a sheet tab and "select all sheets".

CTRL + a(twice in 2003) to select all cells on all sheets.

Copy then in place.........Paste SpecialValuesOKEsc.

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP


On Sun, 28 Oct 2007 19:51:01 -0700, skysusan
wrote:

How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.

write the following macro
Sub FormulasToValues()
Application.ScreenUpdating = False
Application.EnableEvents = False

WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
RCount = ActiveCell.SpecialCells(xlLastCell).Row
CCount = ActiveCell.SpecialCells(xlLastCell).Column
For J = 1 To RCount
For k = 1 To CCount
Worksheets(WCount - i + 1).Cells(J, k) = Worksheets(WCount - i +
1).Cells(J, k).Value
Next k
Next J
End If
Next i

Application.ScreenUpdating = False
Application.EnableEvents = False

End Sub


"skysusan" wrote:

How to save the Excel file with the value only? The excel file is setup with
formula drawing from external data. Once data is drawn, need to save the copy
& distribute it.