Export Excel Worksheets (from list of sheet names) to separete excel file with VBA

I don't have the reputation points to comment, so I can't determine what you require specifically. Hopefully you can get an idea from my answer.

  1. Each worksheet you want to export, is exported to its own (new) workbook (*.xlsx)
  2. You will keep a copy of the exported worksheet in the current workbook

Note, this does not handle exceptions (like missing sheets, name mismatches, . )

Sub ExportSheets() Dim wksWorksheets As Excel.Sheets Dim rngSheetNames As Excel.Range, rngSheet As Excel.Range Dim strSheetTitle As String, strSavePath As String strSavePath = "DriveLetter:\path\to\save\goes\here\" ' Change this to your path Set wksWorksheets = Excel.ThisWorkbook.Worksheets Set rngSheetNames = wksWorksheets("Sheet1").Range("A1:A50") ' Put the specific sheet and range here For Each rngSheet in rngSheetNames strSheetTitle = rngSheet.Value With wksWorksheets(strSheetTitle) .Copy .SaveAs Filename:= strSavePath + strSheetTitle + ".xlsx" End With Next rngSheet End Sub

If this is not what you specifically need, it should not be to difficult to modify the code to your requirements.

Edit: To export to the same existing workbook. Note, the workbook must exist before exporting.

Sub ExportToWorkbook() Dim wkbExportBook As Excel.Workbook Dim wksWorksheets As Excel.Sheets Dim rngSheetNames As Excel.Range, rngSheet As Excel.Range Dim strSheetTitle As String, strWkBookPath As String Excel.Application.ScreenUpdating = False strWkBookPath = "DriveLetter:\path\to\workbook\goes\here\ExportBook.xlsm" ' Change this to your path Set wksWorksheets = Excel.ThisWorkbook.Worksheets Set rngSheetNames = wksWorksheets("Sheet1").Range("A1:A50") ' Put the specific sheet and range here Set wkbExportBook = Excel.Workbooks.Open(strWkBookPath) For Each rngSheet In rngSheetNames strSheetTitle = rngSheet.Value wksWorksheets(strSheetTitle).Copy Before:=wkbExportBook.Sheets(1) ' Before the first sheet or any sheet you want, just pass the sheet index Next rngSheet wkbExportBook.Close SaveChanges:=True Excel.Application.ScreenUpdating = True End Sub

If you require a new workbook for an export batch of sheets, look at the function,

Excel.Workbooks.Add strWorkBookPathName

You should be able to set this to a Workbook object variable.