Using macros to create a sheet for each unique value in a list.

[emily2025]

I have a list of over 500 entries by customer name and I want to be able to create a sheet for each unique customer using macros. Is there a way to do this and to name each sheet based on the customer as well?

I am new to VBA but I tried using two separate strings of code (one to sort through the column, the other to create sheets) but something went wrong along the way.

 

[dmt32]

Hi welcome to forum

Assuming that your data has a row with field names ( header row) then give this code a try & see if does what you want:

Place code in a STANDARD module.

 Option Explicit

Sub FilterData()
'DMT32 2017
    Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range, objRange As Range
    Dim rowcount As Long
    Dim colcount As Integer, FilterCol As Integer, FilterRow As Long
    Dim SheetName As String, msg As String
    
    
'master sheet
    Set ws1Master = ActiveSheet
    
'select the Column filtering
top:
    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If
    
    FilterCol = objRange.Column
    FilterRow = objRange.Row
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    On Error GoTo progend
    
'add filter sheet
    Set wsFilter = Sheets.Add
    
    With ws1Master
        .Activate
        .Unprotect Password:=""  'add password if needed
        
        rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
        colcount = .Cells(FilterRow, .Columns.Count).End(xlToLeft).Column
        
        If FilterCol > colcount Then
            Err.Raise 65000, "", "FilterCol Setting Is Outside Data Range.", "", 0
        End If
        
        Set Datarng = .Range(.Cells(FilterRow, 1), .Cells(rowcount, colcount))
        
'extract Unique values from FilterCol
        .Range(.Cells(FilterRow, FilterCol), .Cells(rowcount, FilterCol)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=wsFilter.Range("A1"), Unique:=True
        
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
        
'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
        
        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
            
'check for blank cell in range
            If Len(FilterRange.Value) > 0 Then
                
'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
'ensure tab name limit not exceeded
                SheetName = Trim(Left(FilterRange.Value, 31))
                
'check if sheet exists
                On Error Resume Next
                Set wsNew = Worksheets(SheetName)
                If wsNew Is Nothing Then
'if not, add new sheet
                    Set wsNew = Sheets.Add(after:=Worksheets(Worksheets.Count))
                    wsNew.Name = SheetName
                Else
'clear existing data
                    wsNew.UsedRange.ClearContents
                End If
                On Error GoTo progend
'apply filter
                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                CopyToRange:=wsNew.Range("A1"), Unique:=False
                
            End If
            wsNew.UsedRange.Columns.AutoFit
            Set wsNew = Nothing
        Next
        
        .Select
    End With
    
    
progend:
    wsFilter.Delete
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
    
    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub

When code is run, you will be presented with an inputbox - using the mouse - select the field name (header) you want to filter & then press OK button.

Code should filter all matching records for each value (customer name) in the column to their own sheet - if sheet does not already exist, code will create it. Data on existing sheets will be cleared & refreshed with each use of the code.

Hope Helpful

Dave

 

[JLGWhiz]

Hi emily2025, Welcome to the Forum. You should always be specific about your columns, rows, sheet names and workbook names. It helps us to write useable code the first time instead of bantering back an forth. Otherwise, we have to make a lot of assumtions to formulate the code. Here is some code based on the assumption that your list is in column A beginning on row 2. we will assume row 1 is a header row.

Sub addSheets()
Dim c As Range, sh As Worksheet
With ActiveSheet
    For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
        sh.Name = c.Value
        Set sh = Nothing
    Next
End With
End Sub

Copy this code to your standard code module 1. Be sure you workbook is saved as a macro enabled workbook.

 

[KevenMeehan]

Hi welcome to forum

Assuming that your data has a row with field names ( header row) then give this code a try & see if does what you want:

Place code in a STANDARD module.

 Option Explicit

Sub FilterData()
'DMT32 2017
    Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range, objRange As Range
    Dim rowcount As Long
    Dim colcount As Integer, FilterCol As Integer, FilterRow As Long
    Dim SheetName As String, msg As String
   
   
'master sheet
    Set ws1Master = ActiveSheet
   
'select the Column filtering
top:
    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If
   
    FilterCol = objRange.Column
    FilterRow = objRange.Row
   
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
   
    On Error GoTo progend
   
'add filter sheet
    Set wsFilter = Sheets.Add
   
    With ws1Master
        .Activate
        .Unprotect Password:=""  'add password if needed
       
        rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
        colcount = .Cells(FilterRow, .Columns.Count).End(xlToLeft).Column
       
        If FilterCol > colcount Then
            Err.Raise 65000, "", "FilterCol Setting Is Outside Data Range.", "", 0
        End If
       
        Set Datarng = .Range(.Cells(FilterRow, 1), .Cells(rowcount, colcount))
       
'extract Unique values from FilterCol
        .Range(.Cells(FilterRow, FilterCol), .Cells(rowcount, FilterCol)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=wsFilter.Range("A1"), Unique:=True
       
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
       
'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
       
        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
           
'check for blank cell in range
            If Len(FilterRange.Value) > 0 Then
               
'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
'ensure tab name limit not exceeded
                SheetName = Trim(Left(FilterRange.Value, 31))
               
'check if sheet exists
                On Error Resume Next
                Set wsNew = Worksheets(SheetName)
                If wsNew Is Nothing Then
'if not, add new sheet
                    Set wsNew = Sheets.Add(after:=Worksheets(Worksheets.Count))
                    wsNew.Name = SheetName
                Else
'clear existing data
                    wsNew.UsedRange.ClearContents
                End If
                On Error GoTo progend
'apply filter
                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                CopyToRange:=wsNew.Range("A1"), Unique:=False
               
            End If
            wsNew.UsedRange.Columns.AutoFit
            Set wsNew = Nothing
        Next
       
        .Select
    End With
   
   
progend:
    wsFilter.Delete
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
   
    If Err > 0 Then MsgBox (Error(Err)), 16, "Error"
End Sub

When code is run, you will be presented with an inputbox - using the mouse - select the field name (header) you want to filter & then press OK button.

Code should filter all matching records for each value (customer name) in the column to their own sheet - if sheet does not already exist, code will create it. Data on existing sheets will be cleared & refreshed with each use of the code.

Hope Helpful

Dave

Hi Dave,

You have created a great query here, but I am running into a problem with my specific version and I am wondering if you are able to assist.

I have columns A:AH, but the data that I am filtering on is in column A. I have made the necessary changes below, but have run into a problem.

Option Explicit
' Credit: DHM32

Sub FilterDataPlacement()
'switch to general
   Windows("data.xlsm").Activate
  
'paste as values
    Columns("B:B").Select
    Selection.Replace What:="/", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

'begin sub
    Dim ws1Master As Worksheet, wsNew As Worksheet, wsFilter As Worksheet
    Dim Datarng As Range, FilterRange As Range, objRange As Range
    Dim rowcount As Long
    Dim colcount As Integer, FilterCol As Integer, FilterRow As Long
    Dim SheetName As String, msg As String
    Dim location As String
    
    
'master sheet
    Set ws1Master = ActiveSheet
    
'select the Column filtering
top:
    On Error Resume Next
    Set objRange = Application.InputBox("Select Field Name To Filter", "Range Input", , , , , , 8)
    On Error GoTo 0
    If objRange Is Nothing Then
        Exit Sub
    ElseIf objRange.Columns.Count > 1 Then
        GoTo top
    End If
    
    FilterCol = objRange.Column
    FilterRow = objRange.Row
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    On Error GoTo progend
    
'add filter sheet
    Set wsFilter = Sheets.Add
    
    With ws1Master
        .Activate
        .Unprotect Password:=""  'add password if needed
        
        rowcount = .Cells(.Rows.Count, FilterCol).End(xlUp).Row
        colcount = .Cells(FilterRow, .Columns.Count).End(xlToLeft).Column
        
        If FilterCol > colcount Then
            Err.Raise 700000, "", "FilterCol Setting Is Outside Data Range.", "", 0
        End If
        
        Set Datarng = .Range(.Cells(FilterRow, 1), .Cells(rowcount, colcount))
        
'extract Unique values from FilterCol
        .Range(.Cells(FilterRow, FilterCol), .Cells(rowcount, FilterCol)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=wsFilter.Range("A1"), Unique:=True
        
        rowcount = wsFilter.Cells(wsFilter.Rows.Count, "A").End(xlUp).Row
        
'set Criteria
        wsFilter.Range("B1").Value = wsFilter.Range("A1").Value
        
        For Each FilterRange In wsFilter.Range("A2:A" & rowcount)
            
'check for blank cell in range
            If Len(FilterRange.Value) > 0 Then
                
'add the FilterRange to criteria
                wsFilter.Range("B2").Value = FilterRange.Value
'ensure tab name limit not exceeded
                SheetName = Trim(Left(FilterRange.Value, 31))
                
'check if sheet exists
                On Error Resume Next
                Set wsNew = Worksheets(SheetName)
                If wsNew Is Nothing Then
'if not, add new sheet
                    Set wsNew = Sheets.Add(after:=Worksheets(Worksheets.Count))
                    wsNew.Name = SheetName
                Else
'clear existing data
                wsNew.UsedRange.ClearContents

                End If
                On Error GoTo progend
'apply filter

                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                CopyToRange:=wsNew.Range("A1"), Unique:=False
                
            End If
            wsNew.UsedRange.Columns.AutoFit
            Set wsNew = Nothing
        Next
        
        .Select
    End With
    
    
progend:
'    wsFilter.Delete
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With

End Sub

When I execute, It is returning some values and not others. In the below mini-table, this is the way my data is structured. The macro will return all matching data for columns A:C, along with all headers. However, for columns D:AV, only rows such as Licensee X will return (when there is a value in column D), and NOT all rows.

Are you able to assist with this request for help?

 

[KevenMeehan]

Allow me to change the credit source to "DMT32" from "DHM32" - apologies for the name mistake.