Save Excel without formula drawing from external data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Right-click and "ungroup sheets"

Save as a new name.


Gord Dibben MS Excel MVP
 
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
 
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
 
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
 
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
 
Back
Top