Data Clean Up Macro

Can you post data in the output sheet so that we can see what entries you are expecting??

Hi,

Are your posted values correct?

Hi ntate

Will these ALWAYS be at the top of the Worksheet?

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

Hi NTate,

Try this - if you're happy with it, remove the apostrophe in the red code:

Sub CleanitUp(): Dim wp As Worksheet, wa As Worksheet, WF As WorksheetFunction
    Dim r As Long, c As Long, i As Long, j As Long, k As Long, T As String, D
        Set WF = WorksheetFunction
Set wp = Sheets("Adherence Data Paste"): Set wa = Sheets("Adherence ")
 wp.Cells.WrapText = False: wp.Cells.MergeCells = False: wp.Columns.AutoFit
r = wp.Rows.Find("*", , , , xlByRows, xlPrevious).Row
        c = wp.Columns.Find("*", , , , xlByColumns, xlPrevious).Column
        wp.Rows(1).Insert: Set D = wp.Rows(1)
        
        For i = 1 To r + 1
        If WF.CountA(wp.Rows(i)) = 0 Then _
        Set D = Union(D, wp.Rows(i))
        Next i: i = 1
        
        D.EntireRow.Delete

GetTable: k = WF.MAX(1, wa.Rows.Find("*", , , , xlByRows, xlPrevious).Row)
        Do Until wp.Cells(i, 3) = "Agent Adherence Totals Report": i = i + 1
                    If i > r Then
                    'wp.Cells.Clear
                    Exit Sub: End If
        Loop: T = wp.Cells(i + 3, 10)
j = i: Do Until InStr(1, LCase(wp.Cells(j, 6)), "wfm reports"): j = j + 1: Loop
    If InStr(1, LCase(T), "none") Or T = "" Then
    wp.Range("A" & i & ":A" & j).EntireRow.Delete Shift:=xlUp
    r = wp.Rows.Find("*", , , , xlByRows, xlPrevious).Row: GoTo GetTable: End If
                            i = i + 5
ExtractEntries:
    k = k + 1: wa.Cells(k, 1) = wp.Cells(i, 4): wa.Cells(k, 2) = T
    Do: i = i + 1
    If wp.Cells(i, 5) <> "" And wp.Cells(i, 5) <> "Agent" Then
    wa.Cells(k, 3) = wp.Cells(i, 5): wa.Cells(k, 4) = wp.Cells(i - 1, 15)
    wa.Cells(k, 5) = wp.Cells(i - 1, 17): wa.Cells(k, 6) = wp.Cells(i - 1, 20)
    wa.Cells(k, 7) = wp.Cells(i, 24): k = k + 1
    End If
    Loop Until IsDate(wp.Cells(i, 4)) Or i = j
    If i = j Then GoTo GetTable
    k = k - 1: GoTo ExtractEntries
End Sub

Try this:

Sub CleanitUp2(): Dim wp As Worksheet, wa As Worksheet, WF As WorksheetFunction, D
Dim r As Long, c As Long, i As Long, j As Long, k As Long, T As String, DP As String
        Set WF = WorksheetFunction
Set wp = Sheets("Adherence Data Paste"): Set wa = Sheets("Adherence ")
 wp.Cells.WrapText = False: wp.Cells.MergeCells = False: wp.Columns.AutoFit
r = wp.Rows.Find("*", , , , xlByRows, xlPrevious).Row
        c = wp.Columns.Find("*", , , , xlByColumns, xlPrevious).Column
        wp.Rows(1).Insert: Set D = wp.Rows(1)
        
        For i = 1 To r + 1
        If WF.CountA(wp.Rows(i)) = 0 Then _
        Set D = Union(D, wp.Rows(i))
        Next i: i = 1
        
        D.EntireRow.Delete

GetTable: k = WF.MAX(1, wa.Rows.Find("*", , , , xlByRows, xlPrevious).Row)
        Do Until wp.Cells(i, 3) = "Agent Adherence Totals Report": i = i + 1
                    If i > r Then
                    'wp.Cells.Clear
                    Exit Sub: End If
        Loop: T = wp.Cells(i + 3, 10)
j = i: Do Until InStr(1, LCase(wp.Cells(j, 6)), "wfm reports"): j = j + 1: Loop
    If InStr(1, LCase(T), "none") Or T = "" Then
    wp.Range("A" & i & ":A" & j).EntireRow.Delete Shift:=xlUp
    r = wp.Rows.Find("*", , , , xlByRows, xlPrevious).Row: GoTo GetTable: End If
                            i = i + 5
ExtractEntries:
    k = k + 1: DP = wp.Cells(i, 4)
    Do:    i = i + 1
    If wp.Cells(i, 5) <> "" And wp.Cells(i, 5) <> "Agent" Then
    wa.Cells(k, 1) = DP: wa.Cells(k, 2) = T
    wa.Cells(k, 3) = wp.Cells(i, 5): wa.Cells(k, 4) = wp.Cells(i - 1, 15)
    wa.Cells(k, 5) = wp.Cells(i - 1, 17): wa.Cells(k, 6) = wp.Cells(i - 1, 20)
    wa.Cells(k, 7) = wp.Cells(i, 24): k = k + 1
    End If
    Loop Until IsDate(wp.Cells(i, 4)) Or i = j
    If i = j Then GoTo GetTable
    k = k - 1: GoTo ExtractEntries
End Sub

Simply amazing, I feel like I've asked to much. (I would post a different thread, but I feel you understand the problem the best by now, haha.)

When the Macro runs through is there anyway that this formula can be added in the adjacent cells in Column H of the "Adherence Data" tab

=RIGHT(C2,LEN(C2)-FIND(" ",C2))&", "&LEFT(C2,FIND(" ",C2)-1)

This simply changes the format of the name in column C. I'd rather not drag the formula down every time I post data... If possible. Thanks again, Xladept!