Link to home
Create AccountLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

VBA create separate tabs in a workbook based on column C

I need a VBA code to create separate tabs in a workbook based on column C.

Column C has multiple "Jerry", create a new tab "Jerry" and copy the data from A-I.

Then select only data with "Terry" in column C and create a tab with "Terry" and copy all the A-I data.

Can anyone help???

Thank you!

Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

Can you upload a sample workbook?
Please obfuscate any personal or sensitive data.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi!

You can use the following VBA code to create separate tabs in a workbook based on column C:

Sub CreateTabs()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.ActiveSheet ' Change this to the sheet with the data
    
    ' Loop through the cells in column C and create new sheets for each unique name
    For Each cell In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row)
        If cell.Value <> "" Then
            Set rng = ws.Range("A" & cell.Row & ":I" & cell.Row)
            If Not SheetExists(cell.Value) Then ' Check if the sheet exists
                ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = cell.Value ' Create new sheet with name
            End If
            ThisWorkbook.Sheets(cell.Value).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value ' Copy data to sheet
        End If
    Next cell
End Sub

Function SheetExists(sheetName As String) As Boolean ' Check if sheet exists
    SheetExists = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = sheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function

Open in new window



You can modify the code to add more names by adding additional If statements after the If cell.Value <> "" Then line. For example:

If cell.Value = "Jerry" Then
    ' Create new sheet and copy data
ElseIf cell.Value = "Terry" Then
    ' Create new sheet and copy data
End If

Open in new window


And so on and so forth...
Avatar of Euro5

ASKER

Martin,
I get the first tab then Sheet1 with correct data in the sheet but error on the following line.
It doesn't name the sheet then errors.

ActiveSheet.Name = colNames(lngEntry)
Avatar of Euro5

ASKER

Noah,
The first sheet is made but has no header.
There is a Sheet2 created, but it has no data and the error on

                ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = cell.Value ' Create new sheet with name
Please click the "Create Worksheets" button on Sheet1 in the attached workbook. It works perfectly for me but if not for you then we can do some debugging.
29258653.xlsm
Avatar of Euro5

ASKER

Martin - it didn't like the names in my column C.
Maybe too long or characters it didn't like.
No worries with that - easy solution.
THANK YOU AGAIN!!
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you, including a new one concerning Easy Ways to do Hard Things.

Marty - Microsoft MVP 2009 to 2017
        Experts Exchange Most Valuable Expert (MVE) 2015 and 2017
        Experts Exchange Distinguished Expert in Excel 2018, 2021, 2022
        Experts Exchange Distinguished Expert in Microsoft Office 2022
        Experts Exchange Distinguished Expert in VBA 2022
        Experts Exchange Top Expert VBA 2018 to 2022
        Experts Exchange Top Expert Visual Basic Classic 2012 to 2022