MrExcel Publishing
Your One Stop for Excel Tips & Solutions

marro Save_As


Posted by jdenoon@aol.col on May 02, 2001 12:18 PM

I need to setup a macro
1) prompts me with the Save_As windows so I may save my workbook to a dif loction every time
2) after I name the file it will convert all my links to Values.

I was able to convert all my links using the macro recorder but Im not able to get the save_as to prompt me


Posted by Dave Hawley on May 02, 2001 11:55 PM

Hi, Try this code:


Sub SaveAndConvert()
'Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''''''''''''''''
'Show SaveAs Dialog and if Workbook Saved, convert ALL
'Formulas to Values
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim Wsht As Worksheet
Dim bYesNo As Boolean
bYesNo = Application.Dialogs(xlDialogSaveAs).Show
'If bYesNo = False Then Exit Sub


For Each Wsht In ActiveWorkbook.Worksheets
On Error Resume Next
Wsht.Cells.SpecialCells(xlCellTypeFormulas) = _
Wsht.Cells.SpecialCells(xlCellTypeFormulas).Value
Next
End Sub


Dave

OzGrid Business Applications