excel vba runtime error 5 invalid procedure call or argument

Question

Hi

I have this enormous and clumsy macro that select a adviser name in page field in pivotTable1,  copies 5 pivot tables (they are connected with a slicer)  one by one to a "Format Report" sheet and paste as values and then

1.  Formats the sheet.

2. Copy the sheet to a new sheet and rename the sheet to the value in P2

then back to sheet "Transaction report" to pick the next name, until each adviser has a sheet .

This worked perfectly until today when I put new data in the data source table, now I get "runtime error 5 invalid procedure call or argument" after 5 sheets are created. The line highlighted is :  pt.PageFields("Adviser").CurrentPage = pi.Name.

I have read other feeds about spaces in sheet names but can not figure this out. 

Most of the macro was recorded.

Please help

Es

Sub SelectCopyPivotsToFormatSheet()

 Dim pt As PivotTable

 Dim pi As PivotItem

Application.ScreenUpdating = False

'Pivot Loop

Sheets("Transaction Report").Select

Set pt = ActiveSheet.PivotTables("PivotTable1")

  For Each pi In pt.PageFields("Adviser").PivotItems

      pt.PageFields("Adviser").CurrentPage = pi.Name

'Copy Prodcut provider:

     Sheets("Transaction Report").Select

    Range("A12").Select

    ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True

    Selection.Copy

    Sheets("Format Report").Select

    Range("A12").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

  'Copy Transaction Type

  Sheets("Transaction Report").Select

  Range("A205").Select

    ActiveSheet.PivotTables("PivotTable4").PivotSelect "", xlDataAndLabel, True

    Selection.Copy

    Sheets("Format Report").Select

    Range("A205").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

  'Copy Product Catagory

  Sheets("Transaction Report").Select

  Range("A503").Select

    ActiveSheet.PivotTables("PivotTable5").PivotSelect "", xlDataAndLabel, True

    Selection.Copy

    Sheets("Format Report").Select

    Range("A503").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

    'Copy Detail

    Sheets("Transaction Report").Select

    Range("A601").Select

    ActiveSheet.PivotTables("PivotTable6").PivotSelect "", xlDataAndLabel, True

    Selection.Copy

    Sheets("Format Report").Select

    Range("A601").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

   'Move Catlculation columns

   Range("B13:C598").Select

    Selection.Cut

    Range("J13").Select

    ActiveSheet.Paste

    'Delete Pivot Heading Lines and format headings

    Range("12:12,205:205,503:503,601:601").Select

    Range("A601").Activate

    Selection.Delete Shift:=xlUp

  'Conditional format grand total rows

  Range("A13:K598").Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=$A13=""Grand Total"""

    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlNone

    Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlNone

    With Selection.FormatConditions(1).Borders(xlTop)

        .LineStyle = xlContinuous

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.FormatConditions(1).Borders(xlBottom)

        .LineStyle = xlContinuous

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.FormatConditions(1).Interior

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorLight2

        .TintAndShade = 0.599963377788629

    End With

    Selection.FormatConditions(1).StopIfTrue = False

'Format Headers

     Range("A12:K12,A204:K204,A501:K501,A598:K598").Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ThemeColor = 3

        .TintAndShade = -0.249946592608417

        .Weight = xlMedium

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ThemeColor = 3

        .TintAndShade = -0.249946592608417

        .Weight = xlMedium

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ThemeColor = 3

        .TintAndShade = -0.249946592608417

        .Weight = xlMedium

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ThemeColor = 3

        .TintAndShade = -0.249946592608417

        .Weight = xlMedium

    End With

    Selection.Borders(xlInsideVertical).LineStyle = xlNone

    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    With Selection.Interior

        .PatternColorIndex = xlAutomatic

        .ThemeColor = xlThemeColorDark2

        .TintAndShade = -0.499984740745262

        .PatternTintAndShade = 0

    End With

'Copy and format header

 Sheets("Transaction Report").Select

 Range("A1:K6").Select

    Selection.Copy

    Sheets("Format Report").Select

    Range("A1").Select

    ActiveSheet.Paste

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Columns("F:F").Select

    Application.CutCopyMode = False

    With Selection

        .WrapText = True

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    Columns("G:G").ColumnWidth = 6.55

    Columns("H:K").Select

    Selection.ColumnWidth = 12

'Delete blanc lines

     Sheets("Transaction Report").Select

    Columns("O:O").Select

    Selection.Copy

    Sheets("Format Report").Select

    ActiveWindow.SmallScroll ToRight:=1

    Columns("O:O").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Selection.AutoFilter

    ActiveSheet.Range("$O$1:$O$3167").AutoFilter Field:=1, Criteria1:="="

    Rows("4:2000").Select

    Selection.SpecialCells(xlCellTypeVisible).Select

    Selection.Delete Shift:=xlUp

    ActiveSheet.Range("$O$1:$O$1307").AutoFilter Field:=1

    Columns("O:O").Select

    Selection.Delete Shift:=xlToLeft

    Sheets("Transaction Report").Select

    Range("A8:C9").Select

    Selection.Copy

    Sheets("Format Report").Select

    Range("A8").Select

    ActiveSheet.Paste

    Range("B8").Select

     Range("C8:C9").Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Application.CutCopyMode = False

    Range("a1").Select

    'Move Report to a New sheet

     ActiveSheet.Copy , Sheets(Sheets.Count)

    ActiveSheet.Name = ActiveSheet.Range("G2")

    ActiveWindow.ScrollColumn = 1

    ActiveSheet.Range("A1").Select

    Sheets("Format Report").Select

    Sheets("Format Report").Cells.Clear

    Sheets("Transaction Report").Select

Next pi

Application.ScreenUpdating = True

End Sub

Answer 1

Es,

There are still old values in the cache of the pivottable(s), as there is "HORN,EA,MR".

This is not an adviser (anymore; he became "HORNE,EA,MR"), and results in the error.

To empty the cache for all PivotTables, use this code once:

Private Sub ResetPivotCaches()

    Dim Pt As PivotTable

    Dim Ws As Worksheet

    Dim Pc As PivotCache

    For Each Ws In ActiveWorkbook.Worksheets

        For Each Pt In Ws.PivotTables

            Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next

    Next

    For Each Pc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        Pc.Refresh

    Next

End Sub

then the property MissingItemsLimit is set for all PivotTables to None.

I think this is is enough for future use, but I'm not sure of that.

If not, you can repeat this code on opening the workbook.

Jan

Answer 2

This has nothing to do with sheet names, we need to see your file.

Make a copy of your original file and anonymize the necessary data.

If you want to automate this step, please download this file

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAn...

Open your Excel file

Right-click on the sheet tab

Choose "View Code"

Press CTRL-M

Select the downloaded file and import

Close the VBA editor

Select the cells with the confidential data

Press Alt-F8

Choose the macro

Click Run

Upload your file on OneDrive (or an other Online File Hoster of your choice) and post the download link here.

https://support.office.com/en-us/article/Share-...

Then we can look at the file and try to find a solution. Thank you for your understanding.

Andreas.

Answer 3

Dear Andreas

Thank you for your help.  I give up.

I read about a loop that runs through slicer items, that might be also a option. 

Please note none of the pivots are saved with source data.

One Drive link: 

https://1drv.ms/x/s!Asqq_iRORDhbgQU-qHFjlevyq_3Y

Kind Regards

Es

Answer 4

Thank you very much.

Answer 5

Did not work, I need a 1:1 copy, see screenshot. Please zip the file and try again or use a different file hoster.

Andreas.