More 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

 

[Ron de Bruin]

Hi Glen

After the copy line add this lines

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False

See the Excel vba help for SaveAs

XlFileFormat can be one of these XlFileFormat constants.
xlCSV
xlCSVMSDOS
xlCurrentPlatformText
xlDBF3
xlDIF
xlExcel2FarEast
xlExcel4
xlAddIn
xlCSVMac
xlCSVWindows
xlDBF2
xlDBF4
xlExcel2
xlExcel3
xlExcel4Workbook
xlExcel5
xlExcel7
xlExcel9795
xlHtml
xlIntlAddIn
xlIntlMacro
xlSYLK
xlTemplate
xlTextMac
xlTextMSDOS
xlTextPrinter
xlTextWindows
xlUnicodeText
xlWebArchive
xlWJ2WD1
xlWJ3
xlWJ3FJ3
xlWK1
xlWK1ALL
xlWK1FMT
xlWK3
xlWK3FM3
xlWK4
xlWKS
xlWorkbookNormal
xlWorks2FarEast
xlWQ1
xlXMLSpreadsheet

 

[Dave Peterson]

See one more reply to your other post.

Similar to Ron's, but slightly different.