top of page

How to Create Multiple Worksheets from a List Using VBA Macros (With Excel File)

Understanding VBA Macros

Creating multiple worksheets from a list using VBA macros in Excel can significantly enhance your productivity and data organization efforts. VBA macros allow you to automate the process of generating individual worksheets based on the items in your list, saving you valuable time and effort. This automation not only streamlines the task but also ensures accuracy and consistency in the resulting worksheets.


By following a structured tutorial that guides you through the steps of creating VBA macros for this purpose, you can gain a deeper understanding of how to leverage Excel's capabilities effectively. The tutorial will provide you with insights into the intricacies of VBA programming, empowering you to customize the macros to suit your specific requirements.


With this newfound knowledge, you will be able to easily adapt the VBA macros to handle various types of lists and data structures, further expanding the utility of this automation technique. This hands-on approach to learning will enable you to master the art of generating multiple worksheets from a list in Excel, equipping you with a valuable skill that can be applied to a wide range of data management tasks.





Preparing Your Data

Creating the List

  1. Open your Excel workbook.

  2. In a column, create a list of names that you want to use for your worksheets. For example, in column A, enter the names in cells A2 to A21.


Opening the VBA Editor

  1. Press Alt + F11 to open the VBA editor.

  2. In the VBA editor, insert a new module:

  • Go to Insert > Module. and pase above code into editor and save it

Writing the VBA Macro

Sub Addsheetsfromselection()
    Dim CurSheet As Worksheet
    Dim Source As Range
    Dim c As Range
    
    Set CurSheet = ActiveSheet
    Set Source = Selection.Cells
    Application.ScreenUpdating = False
    
    For Each c In Source
        sName = Trim(c.Text)
        If Len(sName) > 0 Then
            Worksheets.Add After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = sName
        End If
    Next c
    CurSheet.Activate
    Application.ScreenUpdating = True
End Sub

Step By Step Explanation Multiple Worksheets from a List Using VBA Macros

This VBA (Visual Basic for Applications) macro, named AddSheetsFromSelection, is designed to automate the process of adding new worksheets to an Excel workbook based on the text content of selected cells. Let's break down the code step by step to understand how it works:

Step 1: Declare Variables

Dim CurSheet As Worksheet
Dim Source As Range
Dim c As Range

  • CurSheet: This variable is declared as a Worksheet object. It will hold a reference to the currently active worksheet at the time the macro starts running.

  • Source: Declared as a Range object, this variable will refer to the range of cells selected by the user before running the macro.

  • c: Also declared as a Range object, this variable will be used within a loop to refer to individual cells within the selected range (Source).


Step 2: Initialize Variables

Set CurSheet = ActiveSheet
Set Source = Selection.Cells
Application.ScreenUpdating = False

  • CurSheet: Is set to refer to the currently active worksheet. This is important because later in the macro, new worksheets will be added, potentially changing the active sheet.

  • Source: Is set to the cells within the current selection. Selection.Cells ensures that every individual cell within the selected range is considered, even if the selection is discontinuous.

  • Application.ScreenUpdating: Set to False, this line prevents Excel from updating the screen until the macro finishes running. This improves performance and prevents flickering during execution.


Step 3: Loop Through Selected Cells

For Each c In Source
    sName = Trim(c.Text)
    If Len(sName) > 0 Then
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = sName
    End If
Next c

This loop iterates over each cell (c) in the selected range (Source). For each cell:

  • Trim(c.Text): Extracts the text content of the cell, removing leading and trailing spaces.

  • If Len(sName) > 0 Then: Checks if the trimmed text (sName) is not empty. If there is text, it proceeds to add a new worksheet.

  • Worksheets.Add After:=Worksheets(Worksheets.Count): Adds a new worksheet at the end of the workbook.

  • ActiveSheet.Name = sName: Names the newly added worksheet using the text extracted from the cell (sName).


Step 4: Restore Original Sheet and Screen Updating

CurSheet.Activate
Application.ScreenUpdating = True

  • CurSheet.Activate: Reactivates the original worksheet that was active before the macro started running. This brings the user back to where they were working.

  • Application.ScreenUpdating: Set back to True, this line re-enables screen updating, allowing Excel to refresh the display and show the changes made by the macro.



Executing VBA Code

Step 1: Select the List Range

  1. Open your Excel workbook.

  2. Select the range of cells containing the list of names you want to use to create the new worksheets. For example, if your list is in cells A2:A21, select this range.

Step 2: Access the Macros

  1. Go to the View tab on the Ribbon.

  2. Click on Macros in the Macros group.

  3. Select View Macros from the dropdown menu.


Step 3: Run the VBA Macro

  1. In the Macro dialog box, you will see a list of available macros.

  2. Find and select ThisWorkbook.Addsheetsfromselection.

  3. Click on Run to execute the macro.


Step 4: Verify the Output

  1. Once the macro has been executed, check your workbook.

  2. You should see multiple new worksheets created, each named after the entries in your selected list.

Summary

The AddSheetsFromSelection macro automates the creation of new worksheets based on the text content of selected cells. It temporarily disables screen updating for performance, iterates through each selected cell to add and name worksheets accordingly, and finally restores the original active sheet and re-enables screen updating. This macro is useful for quickly generating multiple worksheets with names derived from cell values, streamlining workflow in Excel.



Download Excel File

Blank File

Solution With Code



Watch Tutorial


bottom of page