Results 1 to 6 of 6

Thread: Data from one sheet auto migrating to another ...

  1. #1
    Seeker AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    12
    Articles
    0
    Excel Version
    365

    Data from one sheet auto migrating to another ...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    .
    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 :
    Attached Files Attached Files

  3. #3
    Seeker AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    12
    Articles
    0
    Excel Version
    365
    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?

  4. #4
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    .
    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.

  5. #5
    Seeker AndyDuncombe's Avatar
    Join Date
    May 2016
    Posts
    12
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Logit View Post
    .
    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.
    Appreciated.

  6. #6
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    277
    Articles
    0
    Excel Version
    2007
    You are welcome.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •