VBA to loop within a loop of multiple blocks of data (currentregion) ... Expert needed

Like many, I have helped others on this site when I can. But sometimes I need help too when the project is over my head. So, I come to the experts for some assistance on this.

I have been trying to do this for days, but can't get too far on this. While I can do a simple code that can loop through one block of data, I can't write a code that loops through multiple blocks of data, and copy specific cells to another sheet. I've never had to do that before, so I need help, please.

I have a spreadsheet with multiple blocks of data (by vehicle, and by date). I need to loop through each block of data for each date and find either of two key words, "Base" or "Base2". If neither word is found, then do nothing and continue to the next block of data.

If either word, "Base" or "Base2" is found in Column G of a block of data, I need the code to capture the starting time in Column A within that block of data, as well as the ending time in column E of that same block of data. In the attached file, I have colored these two cells (Start and End Time) of each instance with red font. These two cells and the cell with the key word will be copied to Columns D, E and H of the Summary sheet along with the part below.

Also, when the word "Base" or "Base2" is found in Column G of the main sheet, the code should capture the time that is in Column A of that same row. Then continue down Column G looking for the word, "Head Quarters" When "Head Quarters" is found, the code should capture the time that is in Column A and Column E of that same row. These three values of time, and the cells containing the words "Base", "Base2" and "Head Quarters" should be copied to the next available (last) row of the Summary Sheet along with the Start and End time from the paragraph above. On the main sheet of the attached file, I have colored the cell font with green.

All these non-contiguous cells should be copied to the next available row on the Summary Sheet, by vehicle, into Columns D, E, G, H, I, K and L)

Could the Vehicle and Driver information also be written to Column A and B the Summary sheet, as well as the date (which is derived from the cell in Column A on the main sheet) written to Column C of the Summary sheet?

The Summary sheet should be grouped by vehicle and by date for each of the instances the vehicle went to "Base" or "Base2" and then to "Head Quarters"

Could each vehicle group be separated by two rows please?

For clarification, I have depicted the desired result I'm seeking on the Summary sheet of the attached file.

I hope I have clearly provided all the details of my request.

Any assistance or help is greatly appreciated.
Thank you!

Added comment: Column P contains some of what I have done manually just to be able to create the Summary sheet. Column P is not part of the original file.

Please Login or Register  to view this content.

AlphaFrog,

Thank you so much! That is amazing and brilliant. You nailed it perfectly! I wasn't expecting the borders, so that is the icing on the cake!

Again, thank you so much for your time and effort. I'm overwhelmed and thankful for your generosity and benevolence.

You're welcome. Thanks for the feedback.