Export Sheets with VBA with Application.SaveAs
A tool purpose is to generate an input for another program or dashboard.
Input files are mostly requested as text .txt file or as excel .xlsx file.
Using a macro we can export sheets from excel.
Export sheets as .xlsx file
This program uses a macro that will do the export of multiple spreadsheets from current workbook. Using the Application.SaveAs the new workbook is saved as excel file.
Sub ExportMultipleSheets_SaveAsDialog()
Dim wb As Workbook, InitialFileName As String, fileSaveName As String
InitialFileName = ThisWorkbook.Path & "\ - Recon_Output_ " & Format(Date, "yyyymmdd")
Sheets(Array("Final List", "Copy Original")).Copy ' complete as many sheets as you want to export
Set wb = ActiveWorkbook
fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitFileName, _
filefilter:="Excel files , *.xlsx")
With wb
If fileSaveName <> "False" Then
.SaveAs fileSaveName
.Close
Else
.Close False
Exit Sub
End If
End With
End Sub
Leave a Reply
Want to join the discussion?Feel free to contribute!