Team Sport Registration File

[benchris]

I am wondering what the best way would be to handle this. I have a team registration list which is shown below in abbreviated form. What I am trying to figure out is the best way to populate another tab based on Column A selection and then copy the rows for that selection to the appropriate sheet. I have separate sheets for Girls 3rd-8th and Boys 3rd-8th. Each grades registration would need to get moved to the correct sheet. With the list below there is one registration for each grade, and after copying in the registrations each row would get populated onto the correct sheet.

Curious if you have any way to accomplish this. I have done it with a pivot table and different slicers on each sheet, but it is not as clean as I would like.

Thanks!

Session name

First name

Last name

Jersey Size

Preferred Jersey #

Shooting Shirt Size

Shorts Size

Primary P/G: Name

Primary P/G: Cell phone number

Primary P/G: Email address

Secondary P/G: Name

Secondary P/G: Cell phone number

Willing to Coach

Secondary P/G: Email address

3rd Grade Girls

First1

Last1

Youth Medium

YM

Parent1

1112223333

pemail1

sgname1

sgphone1

Yes

semail1

4th Grade Girls

First2

Last2

Youth Medium

YS

Parent2

1112223333

pemail2

sgname2

sgphone2

Yes

semail2

5th Grade Girls

First3

Last3

Youth Large

YL

Parent3

1112223333

pemail3

sgname3

sgphone3

Yes

semail3

6th Grade Girls

First4

Last4

Youth Medium

YM

Parent4

1112223333

pemail4

sgname4

sgphone4

Yes

semail4

7th Grade Girls

First5

Last5

Youth Medium

YM

Parent5

1112223333

pemail5

sgname5

sgphone5

Yes

semail5

8th Grade Girls

First6

Last6

Youth Medium

YM

Parent6

1112223333

pemail6

sgname6

sgphone6

Yes

semail6

3rd Grade Boys

First7

Last7

Youth Medium

YM

Parent7

1112223333

pemail7

sgname7

sgphone7

Yes

semail7

4th Grade Boys

First8

Last8

Youth Large

YL

Parent8

1112223333

pemail8

sgname8

sgphone8

Yes

semail8

5th Grade Boys

First9

Last9

Youth Medium

YM

Parent9

1112223333

pemail9

sgname9

sgphone9

Yes

semail9

6th Grade Boys

First10

Last10

Youth Small

YS

Parent10

1112223333

pemail10

sgname10

sgphone10

Yes

semail10

7th Grade Boys

First11

Last11

Adult Small

AS

Parent11

1112223333

pemail11

sgname11

sgphone11

Yes

semail11

8th Grade Boys

First12

Last12

Adult Small

AS

Parent12

1112223333

pemail12

sgname12

sgphone12

Yes

semail12

 

[dmt32]

Hi,
try following code & see if does what you want


Place code in STANDARD module

Option Explicit
Sub FilterColumn()
'dmt32 aug 2020
    Dim wsData      As Worksheet, wsNames As Worksheet, wsFilter As Worksheet
    Dim Datarng     As Range, FilterRange As Range
    Dim rowcount    As Long
    Dim FilterCol   As Variant
    Dim SheetName   As String
    
    On Error GoTo progend
'your master sheet
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    
'Column you are filtering
    FilterCol = "A"
    
    With Application
        .ScreenUpdating = False: .DisplayAlerts = False
    End With
'add filter sheet
    Set wsFilter = ThisWorkbook.Worksheets.Add
    
    With wsData
        .Activate
'add password if needed
        .Unprotect Password:=""
        
        Set Datarng = .Range("A1").CurrentRegion
        
'extract values from FilterCol'to filter sheet
        .Cells(1, FilterCol).Resize(Datarng.Rows.Count).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)
            SheetName = CStr(Left(FilterRange.Text, 31))
'check for blank cell in range
            If SheetName <> "" Then
'add the FilterRange to criteria
'exact matches only
                wsFilter.Range("B2").Formula = "=" & """=" & SheetName & """"
'check if sheet exists
                If Not Evaluate("ISREF('" & SheetName & "'!A1)") Then
'add new sheet
                    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = SheetName
                End If
'set object variable to sheet
                Set wsNames = Worksheets(SheetName)
'clear sheet
                wsNames.UsedRange.Clear
'copy data
                Datarng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsFilter.Range("B1:B2"), _
                CopyToRange:=wsNames.Range("A1"), Unique:=False

'size column widths to match master
'NB - slows code down a little
                Datarng.Rows(1).Copy
                wsNames.UsedRange.Rows(1).PasteSpecial xlPasteColumnWidths
            End If
'clear from memory
            Set wsNames = Nothing
'clear clipboard
            Application.CutCopyMode = False
'clear filter
            If .FilterMode Then .ShowAllData
        Next
        .Select
    End With
    
progend:
    If Not wsFilter Is Nothing Then wsFilter.Delete
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True
    End With
    If Err <> 0 Then MsgBox (Error(Err)), vbCritical, "Error"
End Sub

Code should copy each row to the correct sheet based on Column A value - If sheet does not exist, code will create it.
Change the name of the master sheet shown in BOLD as required

Dave

 

[benchris]

Thanks Dave! That was perfect!

 

[benchris]

How would you go about adding the row of who selected Yes or Maybe in the Willing to coach column to another sheet and pull in their information?

 

[dmt32]

How would you go about adding the row of who selected Yes or Maybe in the Willing to coach column to another sheet and pull in their information?

Hi,
glad solution does what you want

To create a different set of tabs based on another column just change column letter in this line of the code

'Column you are filtering
    FilterCol = "A"

As already stated, code will create the sheets based on Column value.

Dave

 

[benchris]

Thanks Dave! Easy enough. Appreciate the assistance!

 

[dmt32]

Thanks Dave! Easy enough. Appreciate the assistance!

welcome - appreciate feedback

Dave