Separate Sheets Into Workbooks Excel VBA Macro

This macro example separates sheets into new workbooks, keeping the original workbook. Each new workbook takes the name of the sheet and contains one single sheet with its original contents.

Macro/VBA code:

 

  Sub SeparateSheetsIntoWorkbooks()

      Dim ws As Worksheet

      Dim wbPath As String

      wbPath = ThisWorkbook.Path

      For Each ws In ThisWorkbook.Worksheets

          ws.Copy

          With ActiveWorkbook

              .SaveAs Filename:=wbPath & "\" & ws.Name

              .Close

          End With

      Next

  End Sub

 

 

Macro explained:

• We declare a worksheet object variable (ws) that will hold each sheet in the workbook while looping through all worksheets. We also declare a string variable to hold the drive path.
• Then we start a For Each loop to go through each sheet (ws) within the worksheets collection of the workbook.
• For each sheet (ws), we copy the entire worksheet (ws.Copy). This will automatically copy and also paste the sheet’s name and content to a new workbook (note that we do not need to use Workbooks.Add here).
• We save the new workbook (ActiveWorkbook) under the same drive path and with the name of the sheet, and then we close it and loop to the next sheet.

 

This is how we separate sheets into workbooks in Excel with VBA macros.

 

Other examples:

• How to Add Web Query to Worksheet with Excel VBA macros
  
• How to Get Info of Selected Files with Excel VBA macros
• How to Crop Selection Window with Excel VBA macros