Compile information from multiple sheets based on trigger word...again.

  • Part 2:Electric Boogaloo


    I apologize for posting another thread for the same question. Around the time that I was trying to get it solved, the hurricane struck and put us out of commission for a solid week, then an overload of work prevented me from following up on it for a while. Apologies especially to Mumps who was the primary person helping me. I'm reposting because the thread is a month old, and I feel like my attempt at being flexible with a solution instead made the problem too vague & ambiguous, and therefore unnecessarily complicated. Some additional requirements have also been brought up to me, so I can note them in this new thread.


    Old thread link: https://www.ozgrid.com/forum/f…ets-based-on-trigger-word


    Attached is the sample file from the previous thread. It is not exactly like the original, because the original is full of sensitive data. Also, making it more like the original would be a lot of redundant work, because


    Basic script needs:

    • I need to grab the entire row, not specific columns. This is because sometimes notes get put to the right side, and some categories are longer than others.
    • For each category of information, the first row is the category name, second row is generic header, and the third row onwards is
    • The script must stop when cell A is empty. This is due to the fact that there are hidden rows that are empty before the "Total" row. The cell with the word "Total" in it also moves around, so I cannot use that as a constant in the script.
    • Some sheets will not have any rows of information, but will always have the categories and the header below it.
    • Really, the goal is "Check each sheet. If row X has the word 'Category 2', start grabbing every row below it until it finds a row where cell A is empty. Repeat on next sheet, for however many sheets there are. Skip one row for the header." The simpler the better.


    Differences between the sample & the actual file:

    • I put three categories, but there are actually four, There is considerably more information above category 1, specifically in rows 1-4, but if they add more rows with more information, then I won't know it. If they create a new category, then I'd like to be flexible in incorporating that as well.
    • The category names are much different than simply 'category 1'. For example, the first one is actually "Rejects (Active or Cancelled Contracts that did not make it to the system)".


    Again, I apologize for the repeat post. The simpler I can make solving this for mumps or whoever else, the better, please let me know if you need additional information.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!