How to export few sheets of current workbook into a new workbook:

Here we are trying to export few sheets from our current workbook and save it in a new workbook. The new workbook should automatically replace any already available workbook (if present).

Sub ExportSheets()


Application.DisplayAlerts = False
ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Move
ActiveWorkbook.SaveAs "C:\users\AB\NewWorkbook.xlsx", FileFormat:=51, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
ActiveWorkbook.Close (True)


End Sub


Here we are using the move option, which means it will cut the sheets from the existing workbook and paste it in a new workbook and save it by the name NewWorkbook.xlsx.


The clause :
ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
ensures that the sheet will always overwrite any existing file by the same name.


And as we are keeping Application.DisplayAlerts = False
Excel will not prompt users with messages.