next for each copy data based on criteria

  • Hello

    My last thread worked like a charm. However, it seems management has changed their mind about how they would like to see data.

    1. I have data from A to R and approx 1200 rows.

    2. I sort by column P in ascending order

    3. I created 7 Sheets for the main departments

    4. In the P column I have sub categories of all the main departments, which must be copied/pasted to the appropriate sheet..


    This works kinda sorta but as you can see, I have to hard code the sheet name.

    Also, it iterates through all the values in the sheet for each value in my list.

    And of course, the data needs to be appended to the last row on the appropriate sheet.


    Any idea how I can make this happen?


    Thank You

    Terry

  • You mention A:R contains the data but the code is also looking at V2:V11 - what does V2:V11 contain ?

    If it contains main department name then that would be more than the 7 sheets you mention.


    When you say sub-category, is there a column for main department in A:R and another column representing sub-category?


    Your autofilter uses a variable called strSearch but the code provides no idea of what this value could be.


    A sample workbook with some (non-sensitive) data and an example of what the output should look like is always helpful.

  • The v column contains values I typed in, they are sub categories that belong to "Community Services

    "sampledata.xlsx


    Each sub category of needs to go on it's appropriate sheet.

    Col Z contains the a list of unique values for all the Primary divisions.


    The xl book only contains a small number of rows in the alldata sheet.


    See Attachment


    Thank You

    Terry

  • Okay, that helps a bit.


    I can see there are 7 sheets from Community Services through to Senior Management. However, your columns V to X only seem to cover the first 3 of these.


    I am assuming the list of names under each of the columns V to X contain the "sub-categories" you were referring to. But it's not entirely clear where these sub-categories can be found in the main data. Also, I would have expected 7 of these in total with their sub-categories listed in full eg, the Executive column has no sub-categories listed and of course there are no columns for the others eg, Infrastructure, etc.


    Column P seems to mostly represent the "Primary Division" which I've assumed would correspond to 1 of the 7 sheet names. However, there are items in the sample in column P like "Engineering" and "FCSS" that seem to be sub-categories but there is no corresponding primary category (and therefore no idea which sheet) these belong to. It seems column P could contain either a primary or sub-category value.


    It may be an idea to hold the full list of the 7 main departments and their sub-categories in another sheet, just to separate it from the main data. Even if it is retained on the Alldata sheet, I would expect the column headers will need to match the sheet names exactly eg, at the moment cell V1 = Comm Services but the corresponding sheet is actually Community Services.


    You also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.

  • Thank You for your reply, I looked at the workbook, and I have included all info that will be needed (even though the Alldata sheet only has 100 or so records, there are actually over 1200 in the spreadsheet. This will end up being a report every week (hense the need to automate).


    I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets.


    I have "re-organized" the workbook.

    I created a sheet called "Needed Info" that breaks down each department and which "Title" goes on what sheet.

    I have created a sheet called "Com Breakdown" that will breakdown "Community Services" into Managers. For the community services sheet. Example-Managers.xlsx

    I really appreciate the help with this, Thank you

  • OK, there is more information in the new example book but it's still not quite clear how you want to split up the data.


    You have only 7 main departments and there is a sheet for each:


    However, you seem to want to somehow use Column P to split up the data into those 7 sheets.

    In the new example sheet you attached, the following are the unique values in Column P on the Alldata sheet:


    So there seems to be a "lookup table" missing eg which of the 7 sheets does the data for Engineering, Human Resources, IST, etc actually need to be copied to. Some rows in the sample do have a matching sheet name already eg Marketing_Communications.


    What I was excepting see was something like a table with all the sub-categories on the left and the corresponding sheet name on the right eg:


    The left side would of course have to include all possible sub-categories, the above example is just derived from your sample.


    The coding is not too difficult but without a table like this it is difficult to start using the sample data in Column P.


    Also, as per previous post, you also mention "the data needs to be appended to the last row on the appropriate sheet" when you run the macro. However, you either then need to remove the data from the Alldata sheet or add another column that flags that the data has been copied otherwise the data will get copied again if you run the macro more than once.


    You now also say "I will end up removing the B, C, E, G, H, K, L, M, N, Q columns before distributing the data to other sheets." but this could be relatively easily done as part of the macro that copies the data to the 7 sheets.

  • I apologize for any confusion:

    On the "Needed Info" sheet:

    Community Services, Corporate Services, Executive etc are column headings.

    Each value under each column heading matches the values in the O column of the "Alldata" sheet.

    I need to find all occurances of each value on "Needed Info" on the "Alldata" sheet and copy the data to the Appropriate sheet.

    Each column heading on "Needed Info" matches a sheet name. So ever value under the column heading needs to go to the corresponding sheet.


    Ex: sheet "Needed info": Under the column of "Community Services" are several values (Assistant Fire Chief, Bi-law Enforcement, Casual By-law Officer ....etc).

    Each of these values exist in the O column. (Title)

    Filter the "Alldata" sheet for "Assistant Fire Chief", copy all the results to the "Community Services" sheet. This would be done for all values under all columns of the "Needed Info" sheet.


    Ex: sheet "Needed Info". Column "Executive". Look up each value (CAO, Council, Mayor) in the P column of "Alldata". Filter based on the value, Copy to the Executive sheet.

    I made the column headings on the "Needed info" sheet the same as the sheet name in an attempt to make it easier to find the sheet to copy to.


    I hope this adds a little clarity.


    This code is fairly convoluted, But Im trying to figure it out as I go along:


    Thank You

    Terry

  • I have it working (kinda).

    It separates the data the way I want it to (Except breaking down the Community Services sheet).


    Here's the code I've come up with -- likely needs some finessing.

    I still need to remove the columns, autocolumn width and create workbooks for each of the Manager names (community Services).

    But, so far it seems to be going well.


    Thank you

    Terry

  • My sample code is attached which should do what you are trying to do if I have interpreted everything in these posts so far :)


    There are 2 main macros called TransferData and ClearSheets. The Transferdata macro does the following:

    1. Read each of the 7 main categories in the Needed Info sheet, one column at a time
    2. Find the matching rows in the Alldata sheet for all the values in the category (eg, Community Services has 26 sub-categories, Executive has 3 etc)
    3. Copy the matching data to the corresponding category sheet (excluding B, C, E, G, H, K, L, M, N, Q columns)
    4. Minor formatting (auto column widths, freeze panes)
    5. Sort the data by Title and Name in ascending order

    This means every time you run the macro, the data is recreated on the category sheets. As your sample data is limited, some sheets will end up with just a header row with no actual data rows when you run the Transferdata macro.


    ClearSheets can be used if required, it will just delete everything in the category sheets. Not strictly required as the TransferData macro will do this anyway. I have added another button on the Summary sheet that will run this macro.


    Example-Managers.xlsm

  • Please note also that you may need to do some data correction. I noticed there were records in the Alldata sheet that will not match to any of the sub-categories specifically, "Handi Bus Driver" (this should probably be "Handibus Driver" by the look of it) and also "Recreation"



    Also, I happened to notice that eg "Parks Manager" and "Recreation Programming Manager" appear under both Community Services and Senior Management which means any entries for these will be duplicated across the 2 corresponding sheets. I can see some others, this may be intentional.

  • royUK

    Changed the title of the thread from “nest for each copy data based on criteria” to “next for each copy data based on criteria”.