Add rows below pivot tables

I need a macro that would loop through all worksheets and if there is a pivot table or tables on the sheet that the last cell in the pivot table is touching another cell with information in it then the macro would insert 10 rows below the pivot table.

Thanks,
Montrof
LVL 1
montrofAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Try this:
Sub PivotExpander()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rNext As Range
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            With pt.TableRange1
                If Application.CountA(.Offset(.Rows.Count).Resize(1)) Then
                    .Offset(.Rows.Count).Resize(10).EntireRow.Insert
                    With pt.TableRange2
                        .Offset(-1).Resize(.Rows.Count + 11).EntireRow.Group
                        ws.Outline.ShowLevels 1
                    End With
                End If
            End With
        Next pt
    Next ws
End Sub

Open in new window


depending on existing groupings and whether you have pivot tables next to each other horizontally, this may not work!
0
 
Rory ArchibaldCommented:
Try this:
Sub PivotExpander()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rNext As Range
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            With pt.TableRange1
                If Application.CountA(.Offset(.Rows.Count).Resize(1)) Then .Offset(.Rows.Count).Resize(10).EntireRow.Insert
            End With
        Next pt
    Next ws
End Sub

Open in new window

0
 
montrofAuthor Commented:
That is great one last question is there a way to group that data and then close the group.

Thanks,
montrof
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
montrofAuthor Commented:
Sorry I think I need to be a little clearer.  I want to group the new rows and the pivot into one group and then close the group.
0
 
montrofAuthor Commented:
Always amazed at the level of help.  Thank you so much

Montrof
0
 
montrofAuthor Commented:
Sorry one more question it seems like it is not adding the rows below a lot of the pivots.  I want it to add them for any pivot and maybe I miss stated that previously.  I would appreciate your help if you can.
0
 
montrofAuthor Commented:
One more thing if there is not 10 blank rows below the pivot table add them otherwise do nothing
0
 
Rory ArchibaldCommented:
The code only adds rows if there is data in the row immediately below the pivot since that is what you asked for.  :)
0
 
montrofAuthor Commented:
IS there a way I could change it to look for 10 blank rows and if not add them?  If needed I can open another question.  

Thanks,
Montrof
0
 
montrofAuthor Commented:
I figured it out thanks for the help, have a good night.

Montrof
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.