VBA inserting rows around pivot table range

Question

Hello

I hope someone may be able to assist.

I have VBA code (see below) on a specific worksheet that will run when a cell is changed. It will refresh several pivot tables on the single sheet and attempt to insert a row. The insert row  [Selection.Insert]  causes the error message:

Run time error 1004: We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.

However if I make the change on the sheet manually, then that works fine, but the VBA will not perform the same.

The code is:

________________________________________________________________________________________________________________________________

'On Specific worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = True

    If Not (Intersect(Target, Me.Range("RefreshSalary")) Is Nothing) Then

    Application.EnableEvents = False 'to prevent endless loop

    Application.Goto Reference:="RefreshSalary"

        If ActiveCell.Value = "Refresh" Then

           Sheets("Summary").Unprotect Password:=""

           ActiveCell.Offset(0, 0).Activate

           Selection.Insert

           Application.Goto Reference:="RefreshSalary"

           Dim PT As PivotTable

                  For Each PT In ActiveSheet.PivotTables

                  PT.RefreshTable

                  Next PT

           ActiveCell.Value = "Selection"

           Worksheets("Summary").Columns("A:Z").AutoFit

           Sheets("Summary").Protect Password:=""

        End If

    End If

    Application.Goto Reference:="RefreshIncome"

    Application.EnableEvents = True

End Sub

____________________________________________________________________________________________________________________

I know multi-pivot tables on a single sheet is not ideal but this is the users' preferred approach. I guess the VBA thinks the insert position is a pivot table whereas the manual effort doesn't.

Any help will be much appreciated.

Thank you,

Dave

Answer 1

In such a case, try the following:

If your Pivot Table has "Grand Total" as the last line, then use this to find the row number of the "Grand Total"

and insert a row after that row number.

Answer 2

I tried copying your vba code into one sample xlsm file having some sample data.

Unfortunately, the macro did not execute. I believe you have some named ranges defined and used.

Would it be possible for you to share your xlsm file with some sample dummy data (after removing your confidential data) ?

This would help to exactly understand your problem and see if any solution can be derived.

Answer 3

Hi Rajesh

That is exactly what I am doing both manually and via VBA. The table has a Grand Total. If I insert manually on a row after this Pivot Table Grand Total it works. If I carryout the same operation via VBA it does not and I get the error message as stated.

I am going to delete and re-build the pivot tables one by one checking as I go and trust that whatever quirky event is stopping the VBA code executing resolves itself or becomes obvious.

Thanks

Dave

Answer 4

Hi Rajesh

Thanks for your reply.

There are three pivot tables on the sheet and they are ordered one below each other (rows) not side by side (columns).

In manual mode I can physically see the tables and insert rows between them. However the VBA code to do the same thing fires the error message.

I can only guess that the VBA believes the Pivot Table range is larger (more rows) than I can physical see (with no filters applied). Is there a VBA code, do you know, that I could execute to return the range of the Pivot Table? Then if it is not what I can physically see then at least I know where my insert command must start (and couple that maybe with some hide rows).

Thank you.

Dave

Answer 5

If there are multiple Pivot Tables placed Horizontally in the same sheet, then definitely there may be a possibility that the row which you are trying to insert may clash with one of the Pivot Table somewhere on the right hand side.

In such a case, you must first try to get the last row of each Pivot Table and compare the largest last row with the row number which you are trying to insert. 

In short, insert a row only after the last row of the longest Pivot Table.

You may have to insert code in your macro for checking this.

Alternatively, if your Pivot Tables are one below the other and if you are trying to insert a row below (between) and of the two Pivot Tables, then this problem may not arise at all.

Hope this is Helpful.