Trying to show pivot table details into separate sheets

[hashi856]

I'm attempting to show the pivot table details of each row in the table in a different sheet. There is also some formatting going on. My issue is that it is trying to create a sheet for the grand total. I can obviously remove the total, but I might be handing this off to someone in the future, and I want it to be as robust as possible. I got the below code for creating the sheets from an other post (the subsequent formatting is my own code). I've tried googling the DataBodyRange.Resize method, since I don't really understand that first line of the For loop, but I'm not finding anything useful.

Sub Builder_Payroll_Test()

Dim c As Range
    With ActiveSheet.PivotTables(1)
        For Each c In .DataBodyRange.Resize(, 1)
           c.ShowDetail = True
           ActiveSheet.Name = Range("C2")
           Dim listobj As ListObject
           For Each listobj In ActiveSheet.ListObjects
            listobj.Unlist
            Next listobj
           Range("M1:R1").Cells.Interior.Color = 5287936
           Range("M:M, N:N, P:P, R:R").Cells.NumberFormat = "@"
           Range("Q:Q").Cells.NumberFormat = "m/d/yyyy"
           ActiveSheet.Columns.AutoFit

        Next c
    End With

End Sub

 

[=ODIN=]

This might work:

Sub Builder_Payroll_Test()

Dim c As Range
    With ActiveSheet.PivotTables(1)
        For Each c In .DataBodyRange.Resize(, 1)
            'Debug.Print .DataBodyRange.Resize(, 1).Address(0, 0)
            c.ShowDetail = True
            'Debug.Print c.Row & "---" & .DataBodyRange.Offset(.DataBodyRange.Rows.Count, 0).Row
            If c.Row <> .DataBodyRange.Offset(.DataBodyRange.Rows.Count, 0).Row - 1 Then  'if c is not last row of pivot then set sheet name to c2 else sheet name = grand total
                ActiveSheet.Name = Range("C2")
            Else
                ActiveSheet.Name = "Grand Total"
            End If
            
            
            
       
           Dim listobj As ListObject
           For Each listobj In ActiveSheet.ListObjects
            listobj.Unlist
            Next listobj
           Range("M1:R1").Cells.Interior.Color = 5287936
           Range("M:M, N:N, P:P, R:R").Cells.NumberFormat = "@"
           Range("Q:Q").Cells.NumberFormat = "m/d/yyyy"
           ActiveSheet.Columns.AutoFit

        Next c
    End With

End Sub