VBA I'm trying to show pivot table details into separate sheets, but the code is trying to make a sheet for the grand total

[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

 

[severynm]

The DataBody.Resize method you are looking for is actually the Range.Resize method, because PivotTable.DataBodyRange is a Range object (i.e. because it is of type Range, it would inherit all properties and methods of Range, one of which is .Resize).

I'm not super familiar with how pivot tables work exactly, but that first first loop looks over each cell in the first column of the pivot table. If you add some .Select statements and step through with the debugger, you might be able to better see what is happening visually.