Data Clean Up Macro

Hi folks,

So, I have a report that I pull from a back end system. When I import the data into excel it looks quite ugly (see attached).

I essentially need to get rid of all the formatting and pull back only data.

This report breaks up teams into tables, and by date and shows the data for each agent that rolls up to their team leader (depicted in a gray cell).

If possible I would like to be able to paste the data into my "Adherence Data Paste" tab and bring back in my "Adherence" tab the following:

- The date in column A
- The Team name (depicted in gray cell) for the agent in column B (if this is to much a pain I have a fairly simple work around, so I'm not to worried about it.)
-The "Total Time" in column D.
- The "Duration of Non-Adherence" in column E
- The "Duration of Non-Adherence Out of Schedule" in column F
- The "% Adherence in Column G"
- I would like to create the macro for this so that I may assign it to a form button, simply click and be done.

If possible I would like it if there is nothing for the team name (my example has two instances of this, one being blank and the other saying <None>
that the macro simply deletes that table and moves on.

After the data has been copied/moved from the "Adherence Data Paste" tab I would like to delete everything in the "Adherence Data Paste" tab so that it is blank and ready for the next day's data to be pasted in and run through the macro.

Lastly, as I will be storing this data for historical purposes, it would be nice if the macro would paste the data at the bottom of "Adherence" tab below any previously pasted data.

The data as it will be pasted in it's raw form will already be in the "adherence Data Paste" tab as an example.

I know it's a lot, and I appreciate any help!

I have attached another example, this time with a few lines of data in the output sheet.

Obviously I have not entered every line of data that I need, rather I randomly selected lines of data so that way you can get an idea of the expected outcome.

Thanks!

Apologies, everything is correct except for cell g4 in the "Adherence" tab. This should be 100.

Unfortunately no. I have no way of telling if those blank teams will appear or not from day to day.

Oh my gosh xladept, this works perfectly!

I did have one question, when you run the code and it is pasted into the "Adherence" tab do you think it is possible to have the date and team name run down and copy next to each agent? It would essentially have to run down until it hits the next, first, posted date/name in column A/B change to that date/name and then continue to run down. I'll include an example for reference.

Again, thank you so much for all of your help!