Data from one sheet auto migrating to another ...

A

AndyDuncombe

New member
Joined
May 4, 2016
Messages
29
Reaction score
0
Points
1
Excel Version(s)
365
So I run membership for a hockey club and we have over 1000 members.
It's important that the U18 members are put into the correct age grade teams - but even more important to know who has paid their dues and who hasn't.
I have created a workbook with multiple sheets - one sheet for every age group team.
EVERY member is listed on one sheet and I use conditional formatting to identify those who have paid, are pending or or have yet to pay their subscription.
i then manually copy and paste from the ALL sheet to the respective age group, 7, 8, 9 etc.
What I need is for one of you magicians to help me to automatically migrate from the ALL sheet to the relevant age team sheet!

I'm thinking create a data table on the ALL sheet and then use INDEX and MATCH to 'pull' the data?

If I fill column A with "7" on the (team)7 tab I want it to pull all the members with a 7 in column A on sheet ALL, then repeat this for every age group.

I can only just spell pivot table let alone create/use one!

The workbook is attached.
 

Attachments

  • All ages.xlsx
    169 KB · Views: 7
.
The following will accomplish your stated goal ... as I understand it.
This is one method ... there are other methods as well. This code
is hopefully straight forward and perhaps more understandable as it
breaks down the steps for easier understanding.

Code: 
Option Explicit


Sub CreateSheets()


    Dim Cell    As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim Wks     As Worksheet


        Set RngBeg = Worksheets("Sheet1").Range("A2")
        Set RngEnd = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)


        ' Exit if the list is empty.
        If RngEnd.Row < RngBeg.Row Then Exit Sub
Application.ScreenUpdating = False
        For Each Cell In Worksheets("Sheet1").Range(RngBeg, RngEnd)
            On Error Resume Next
                ' No error means the worksheet exists.
                Set Wks = Worksheets(Cell.Value)


                ' Add a new worksheet and name it.
                If Wks.Name <> Cell.Value Then
                    Set Wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
                    Wks.Name = Cell.Value
                End If
            On Error GoTo 0
        Next Cell
Application.ScreenUpdating = True
MakeHeaders
End Sub


Sub MakeHeaders()
Dim srcSheet As String
Dim dst As Integer
srcSheet = "Sheet1"
Application.ScreenUpdating = False
For dst = 1 To Sheets.Count
    If Sheets(dst).Name <> srcSheet Then
    Sheets(srcSheet).Rows("1:1").Copy
    Sheets(dst).Activate
    Sheets(dst).Range("A1").PasteSpecial xlPasteValues
    Sheets(dst).Range("A1:H1").Interior.Color = RGB(84, 129, 53)
    Sheets(dst).Range("A1:H1").Font.Color = vbWhite
    Sheets(dst).Range("A1:H1").Font.Bold = True
    
    Sheets(dst).Range("A1").Select
    End If
Next
Application.ScreenUpdating = True
CopyData
End Sub


Sub CopyData()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
On Error GoTo M
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
    For i = 3 To Lastrow
    ans = Sheets("Sheet1").Cells(i, 1).Value
        Sheets("Sheet1").Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
        Sheets(ans).Columns("A:H").EntireColumn.AutoFit
    Next
Application.ScreenUpdating = True


Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1").Select


Exit Sub


M:
MsgBox "No such sheet as  " & ans & " exist"
Application.ScreenUpdating = True


End Sub

See attached :
 

Attachments

  • Book1.xlsm
    42.6 KB · Views: 9
Hi Logit
Many thanks for your efforts to resolve my question.
In the attached xlsm you returned it seems that once I hit THE "button" the 'master' list containing all data is extracted by age group and these additional sheets are created?
Am I write to assume then that if I update the master list and hit THE "button" this will update each individual sheet?
 
.
The present macros will continue to add the same data to the corresponding sheet. This will duplicate the data already existing on each sheet.

To avoid this duplication you will need to first delete all the data sheets EXCEPT the Master List sheet.
 
Logit said:
.
The present macros will continue to add the same data to the corresponding sheet. This will duplicate the data already existing on each sheet.

To avoid this duplication you will need to first delete all the data sheets EXCEPT the Master List sheet.
Click to expand...

Appreciated.:target:
 
You must log in or register to reply here.
Back
Top