Help with SaveAs

[Glen Mettler]

Trevor put me on the right track to save an individual worksheet
programmatically. However, I still have a problem. When I open the saved
sheet it has the formulas linked back to the parent workbook. How can I
save this with values only and eliminate the link?

Here is the code I am using:
sheetname = ActiveSheet.Name
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs filename:= _
CARTpath & "\" & sheetname & ".xls" _
, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True

also I can't find any information about "FileFormat:=xlNormal" What other
options are availabe besides xlNormal?

Thanks
Glen

 

[Dave Peterson]

add these lines after the activesheet.copy statement:

with activesheet.usedrange
.value = .value
end with

Or record a macro that selects all the cells and copies|pastes special|values.

And put your cursor over .SaveAs in your code.
Hit F1.
Click on "show all" in the top right corner.
click on "fileformat"
click on "xlfileformat"

Or hit F2 in the VBE (to get to the object browser)
search for fileformat.
You should see lots of possibilities.