Save Excel without formula drawing from external data

S

skysusan

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.
 
G

Gord Dibben

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 Special>Values>OK>Esc.

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP
 
S

skysusan

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
 
G

Gord Dibben

Did you try it?

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

If you do, you could copy again and paste special>formats.


Gord
 
S

skysusan

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
 
Y

yusuf

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
 
Top