Add Sheets From Selection Excel VBA Macro

This simple macro example adds as many sheets to the workbook as cells in the selected range. The name of each sheet corresponds to the value in the cell. It is a quick way to format a workbook from a template of sheets to be added.

Macro code:

  
  Sub AddSheetsFromSelection()
 
      Dim cell As Range
 
      'Loop through each cell in selected range
      For Each cell In Selection
          Sheets.Add after:=Sheets(Worksheets.Count)
          ActiveSheet.Name = cell.Value
      Next cell
 
  End Sub
  

Macro explained:

• We declare a Range object variable that will hold each cell in the selected range. The object “Selection” represents the cell or group of cells selected in the Excel worksheet.
• Then we start a For Each loop to go through each cell within the selected range (Selection). Note that “cell” is just an arbitrary name that represents each individual cell (a Range object) in the selection, but we could have used any other name for that.
• Then we add a new sheet after the last sheet in the workbook. The property Worksheets.Count returns the number of sheets, which is used as a parameter for “After”. This is happening as many times it loops, which happens as many times as cells in the selected range.
• The newly added sheet becomes then the active sheet. Then we can just rename that sheet to match the content in the cell by targeting the object “ActiveSheet” and modifying its “Name” property.

 

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

 

Other examples:

• How to Add Multiple Series To Chart with Excel VBA macros
• How to Extract Emails From Outlook with Excel VBA macros
• How to Add Conditional Formatting For Another Cell with Excel VBA macros