Auto copy data from master list to sub worksheets based on data value in one column

  • Hi Guys


    New to VBA, relatively adept with most other parts of excel.


    I have got a macro that takes the data from 2 sub worksheets and copies that across to a master list at the click of a button, but I'm really needing the reverse. I'm needing a macro to copy data inputted into the master list into 2 separate worksheets in same workbook according to what age a person is.


    In a nutshell the master list is the database of all youths that attend sports training. The sub worksheets need to be populated by age ie U12s on one and U14s (or anyone with an age older than 12) on the other . These sub sheets will then be the ones printed to form the base of the termly registers.


    I am aware I could just use the filter buttons on the master list to separate out those rows required, but a couple of less IT literate people will be in the main using the information so it needs to be foolproof and as easy to use as possible, hence the requirement to input into the master and then press one button for that info to be magically split per age category.


    Thanks in advance

  • Hello Arby,


    See if the following code does the task for you:-



    The code uses a helper column (Column X so its out of the way) to define the sheet names from the ages in Column G by using a formula which is inserted by the same code.


    It then transfers the relevant rows of data (from columns A to G) to the relevant age sheet. The age sheets are refreshed each time a transfer of data takes place.


    I've attached your sample file with the code implemented and assigned to the button you placed on the sheet. Click on the button to see the code at work.


    I hope that this helps.


    Cheerio,
    vcoolio. Arby.xlsm

  • Vcoolio


    Thanks a bunch, that is looking pretty much exactly what I was requiring and works perfectly.


    Having run it I can see I really need the 12 YO's to be included in the U14 sheet rather than the U12s, so have tweaked the code to do that (know a teeny teeny bit of coding, but not enough to write macro from scratch). :)


    At the moment there are a number of rows of data that do not have an age in the column, which get pulled across to the U14 worksheet.


    Can you advise on what line to add at/after the IF statement to make the empty G rows be moved to a separate worksheet (lets call it no age)?


    Thanks


    Arby

  • cheers, I was almost there, it was the " " that was getting me, I was thinking more along the lines of a blank or empty statement, didn't think to just have nothing between 2 sets of speech marks!


    Just one thing I've noticed is....my master sheet has filters set on the column header title, as do the sub sheets. When the macro is run via the update button, the filter arrows then vanish from the master sheet.....is there anyway of preventing that from happening as I still need to be able to filter data on the master list as well as the individual group sheets


    Thanks in advance


    A

  • Hi


    The above code has been working great (no surprise as you are all genii :-) )


    However I am now needing to use the same (similar) code, but instead of it being split into 2 sheets depending on the data in Col G ( It was on age) into 3 named sheets based on the data in the column, which has changed.


    The data in the column is now either an S4, S5 or S6.


    So on the click of the button take the master data list and copy it to the relevant worksheet based on if the value in Col G is S4, S5 or S6


    I have tried to tweak the original code, I know the line that needs changing, but I'm not hitting the lucky jackpot it would seem.

    I can get it to copy it to one of the named sheets, but the data doesn't split up.......


    Original code below, and the line that I know I need to change is Line 11 fat the IF function part


    Any help would be very much appreciated as always


  • The spreadsheet is to be used by people who are not spreadsheet savvy....

    In a nutshell the master list is the database of all youths that attend sports training. The sub worksheets need to be populated by age ie U12s on one and U14s (or anyone with an age older than 12) on the other . These sub sheets will then be the ones printed to form the base of the termly registers.


    I am aware I could just use the filter buttons on the master list to separate out those rows required, but a couple of less IT literate people will be in the main using the information so it needs to be foolproof and as easy to use as possible, hence the requirement to input into the master and then press one button for that info to be magically split per age category.


    Thanks in advance

    The only change is rather than the data being copied/split by age group ie U12s, U14s as it was, the master list of details needs to be split by the data in Col G, which is now either an S4, S5 or S6. It is this line in the code that I'm having trouble to redirect to look for that information then to split the relevant rows of data to the relevant sheet.....

  • Hello Arby,


    for the new set up, you may only need this:-



    I hope that this helps.


    Cheerio,

    vcoolio.

  • Great, Thanks again vcoolio.....


    Am I right in saying that if the master data list changed ie had extra columns in, all that would need tweaking would be lines 13 & 14?


    Code
    1. .AutoFilter 7, ar(i) '---> The number would change to the corresponding number of the column in which the data to be filtered by is in
    2. .Columns("A:G").Offset(1).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(3)(2) '---> the range of the columns would be expanded as required ie A - AA
  • That's correct Arby, but I'm assuming that you'll still be using Column 7 ("G") to filter on so leave that line of code alone.


    Also, you may want to add the following line of code:-


    Code
    1. Sheets(ar(i)).UsedRange.Offset(1).ClearContents

    directly after:-


    Code
    1. For i = 0 To UBound(ar)

    This will clear the existing data in the destination sheets prior to the next transfer of data so that you don't end up with a mass of duplicates in the destination sheets (I'm assuming that you'll be keeping all the data in the "Master List" sheet).


    Cheerio,

    vcoolio.

  • Yes will be leaving it as is for now, but the data included on the sheet may change going forward so if the column order changed, then was checking my code interpretation was correct if needed to be changed.


    Genius for the addition....you are indeed correct on your assumption :-)


    :):thumbup: