Not sure if this works for what you need. But I used this and excel file stayed intact. I'm unsure how to save as other fileformats but mayber you can use this. I got this from posters here and love it. I need my excel file intact because it contain a summary of all PO's created, duplicates and otherwise. Plus I can update vendors, accounts , etc and it is a great source file.
Sheets("Purchase Order").Select
Sheets("Purchase Order").copy
Range("A2:q60").Select
Selection.copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Purchase Order").Select
Dim Path As String ' current worksheet path
Dim ThisFileNew As String ' new file name including path
Dim Resp As Integer ' user response to overwrite query
Dim i As Integer ' rename workSHEETS
Target = Range("Q1")'cell contain file name of purchase order, concencated vendorname & PO#
'For new file
Path = ThisWorkbook.Path
If Not Path = "" Then Path = Path & ""
ThisFileNew = Path & Target & " .csv"
Resp = vbOK
' Check for duplicate If Dir(ThisWorkbook.Path & "\" & Target & " .csv") <> "" Then
Resp = MsgBox("You are duplicating files. Are you positive you want to continue ? ", vbExclamation + vbOKCancel)
End If
' Save the workbook if file does not exist, if user wants to overwrite it
If Resp = vbOK Then
ActiveWorkbook.SaveAs Filename:=ThisFileNew
Else
Resp = MsgBox("You will need to rename this file manually and log this overwrite with the accounting department", vbInformation)
End If
ActiveWorkbook.Close'guess your file format name would go here
End Sub
