How do you insert rows in a sheet that has a PivotTable

[Gail Hurn]

I am trying to programatically add rows to a worksheet that has a PivotTable,
but I always get an error.

Here is the code:

Dim sheet As Excel.Worksheet
Dim cell, newCell As Excel.Range

Set sheet = Application.ActiveSheet
Set cell = sheet.Cells(1, 1)
cell.Select
cell.Insert (Excel.XlInsertShiftDirection.xlShiftDown)

This code works if the worksheet does not contain a PivotTable. The
PivotTable is located at cell A3 so it shouldn't be in the way of the
inserted row.

Thanks in advance for any insight on how to make this work.

 

[Mike Fogleman]

Your code is not inserting an entire row, it inserts one cell at the top of
column A. The Pivot Table would then be one row off from the rest of the
table in column A. So it does not allow you to do this.

ActiveSheet.Rows(1).Insert

will insert a new row at row 1, pushing the entire sheet down 1 row and
keeping the table aligned.

Mike F