How to automatically create sheets based on a list using VBA?

Creating sheets automatically in Excel based on a list using VBA can be a useful way to streamline your workflow, especially if you need to manage a large number of worksheets. Below is an example of how you can accomplish this task using VBA:

Sub CreateSheetsFromList()
    Dim ws As Worksheet
    Dim sourceSheet As Worksheet
    Dim sheetName As String
    Dim cell As Range
    Dim listRange As Range
    Dim lastRow As Long

    ' Define the source sheet where the list of names is located
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name if needed

    ' Find the last row in the specified column (assuming the list is in column A)
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

    ' Define the range containing the list
    Set listRange = sourceSheet.Range("A1:A" & lastRow) ' Adjust if the list starts on a different row

    ' Loop through each cell in the list
    For Each cell In listRange
        sheetName = cell.Value

        ' Check if the sheet by this name already exists
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(sheetName)
        On Error GoTo 0

        ' If the sheet does not exist, create a new one
        If ws Is Nothing Then
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = sheetName
        End If
        Set ws = Nothing
    Next cell
    
    MsgBox "Sheets created successfully!", vbInformation
End Sub

How It Works:

Specify the Source Sheet: Modify `sourceSheet` to point to the worksheet containing your list of names.
Determine Location of the List: The script assumes the list is in column A of your source sheet. Adjust the `Range` function if your list is located in another column or starts from a different row.
Ensure Unique Sheet Names: The script checks whether a sheet with the given name already exists before creating a new one to avoid errors.
Add Sheets: For each unique name in the list, a new worksheet is created with that name.
Error Handling: The script uses error handling (via `On Error Resume Next`) to skip over issues like attempting to assign a name that’s already in use.

Remember to adjust the code to fit the structure of your workbook as needed. For instance, the name of the sheet containing the list might need changing from “Sheet1” to whatever its actual name is in your workbook. Additionally, ensure Excel’s macro settings are enabled in order to run this VBA code.

facebooklinkedin