Posts by Arby

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

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

    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


    Thanks, that would be fab too! This is the code being used to split the data by specified column

    Hi Guys


    I have a workbook that has been split into numerous sheets according to specified column data via VBA


    However I need the 1st row of each sheet to have the filter drop down options available. I could go through each sheet individually, highlight the top row and click filter, but as I have about 30 sheets each with numerous columns that's rather time consuming.


    Can someone help me with the coding to do that please. I don't want to filter the data via vba, I just want each sheet to have the top row with the filter drop down buttons applied.


    TIA

    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

    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

    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