Export And Save Selected Sheets To A New Workbook

8 minute read

User can save the new workbook as file types: xlsx, xlsm, xlsb, xls, csv and txt with the following features:

  • external links are broken
  • formulas that reference sheets outside the sheet in the new workbook are changed to values
  • VBA code within modules will not copy to the new workbook however Worksheet codes will copy if the save file type is ‘xls’, ‘xlsm’ and ‘xlsb’
  • option to copy as values, commented out in the code
  • cannot export multiple sheets for csv or txt file types, do these individually
  • loops through each selected sheet and copy to a new workbook. This way you are able to for any protected sheets in the selection. The most popular use of copying to another workbook is ActiveWindow.SelectedSheets.Copy’ however this an error if any of the selected sheets contains a table. This macro bypasses that. Boom!
  • Utilises 3 functions: ExtractWord, SelectedSheetNames, IsFileOpen which are documented separately
' ======================================================================================================
' ## Export Selected Sheets To A New Workbook
' User can save the new workbook as file types: xlsx, xlsm, xlsb, xls, csv, txt
' All external links are broken
' Formulas that reference sheets outside the sheet in the new workbook are changed to values
' VBA code within modules will not copy to the new workbook however Worksheet codes will copy
' if the save file type is 'xls', 'xlsm' and 'xlsb'
' Option to copy as values, commented out in the code
' Cannot export multiple sheets for csv or txt file types, do these individually
' Loops through each selected sheet and copy to a new workbook. This way you are able to
' test for any protected sheets in the selection. The most popular use of copying
' sheets to another workbook is 'ActiveWindow.SelectedSheets.Copy' however this
' throws an error if any of the selected sheets contains a table.
' Utilises 3 functions: ExtractWord, SelectedSheetNames, IsFileOpen which are documented separately
' ======================================================================================================
Sub ExportSelectedSheets() ' Vars Dim wbOriginal As Workbook Dim wbNew As Workbook Dim lngResponse As Long Dim x As Long Dim i As Long Dim lngFileFormat As Long Dim SelectedCount As Long Dim strFileName As String Dim strDialogTitle As String Dim strFolder As String Dim strFormat As String Dim SheetNames As String Dim strSaveFileName As Variant ' Optimize Code Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False ' Set original workbook Set wbOriginal = ActiveWorkbook ' Set up Save as dialog box to return correct file path string strDialogTitle = "Export Selected Sheets to a New Workbook" strSaveFileName = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:= _ "Excel Workbook (xlsx) (*.xlsx), *.xlsx" & _ ",Macro Enabled Workbook (xlsm) (*.xlsm), *xlsm" & _ ",Excel Binary Workbook (xlsb) (*.xlsb), *xlsb" & _ ",Excel 97- Excel 2003 Workbook (xls) (*.xls), *xls" & _ ",CSV (comma delimited) (*.csv), *csv" & _ ",Text File (txt) (*.txt), *txt" _ , Title:=strDialogTitle) ' If User Proceeds with saving the new workbook If strSaveFileName False Then ' Get folder path strFolder = Left(strSaveFileName, InStrRev(strSaveFileName, ")) ' Get the File Format Number of the selected save file type strFormat = LCase(Right(strSaveFileName

Leave a Comment