pi>Visible <Type Mismatch>


New Member
Jul 10, 2019
Respected gurus, experts and MVPs,

I have written the following code for the pivot table and it is generating pi.Visible <Type Mismatch> error.
Sub piVisibleMismatch()
Dim lastrow As Long, endrow As Long
Dim Psheet As Worksheet, Dsheet As Worksheet
Dim Pcache As PivotCache, Ptable As PivotTable, Prange As Range
Dim i As Long, j As Long, pi As PivotItem
Dim currep_date As Date
Application.ScreenUpdating = False

currep_date = "31/10/2019"
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
Set Psheet = Worksheets("PivotTable")
Set Dsheet = Worksheets("Summary")
lastrow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Prange = Dsheet.Range("A2:O" & lastrow)
On Error Resume Next
Set Pcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Prange). _
CreatePivotTable(TableDestination:=Psheet.Cells(6, 1), TableName:="Total_Table")
Set Ptable = Pcache.CreatePivotTable(TableDestination:=Psheet.Cells(6, 1), TableName:="Total_Table")
With ActiveSheet.PivotTables("Total_Table").PivotFields("Balance 2")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlSum
End With
With ActiveSheet.PivotTables("Total_Table").PivotFields("Type of Account")
    .Orientation = xlPageField
    .Position = 1
End With
With ActiveSheet.PivotTables("Total_Table").PivotFields("Collection Date")
    .Orientation = xlPageField
    .Position = 3
End With
With ActiveSheet.PivotTables("Total_Table").PivotFields("Reporting CCY")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("Total_Table").PivotFields("Type of Account").CurrentPage = "Regular"

With ActiveSheet.PivotTables("Total_Table").PivotFields("Collection Date")
    For Each pi In .PivotItems
        If DateValue(pi.Name) > currep_date + 30 Then
            pi.Visible = False
        End If
    Next pi
End With
On Error GoTo 0
ActiveSheet.PivotTables("Total_Table").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("Total_Table").TableStyle2 = "PivotStyleLight2"
Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
ActiveSheet.Cells.Font.Name = "Arial"
ActiveSheet.Cells.Font.Size = 8
ActiveSheet.Range("A:A").ColumnWidth = 35#: ActiveSheet.Range("B:F").ColumnWidth = 15#
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

I had a similar error for the 2 other worksheets but after debugging for more than 5 hours, I could trace the lastrow mismatch error. The lastrow in the source worksheet for the Pivot Table was updated consisting of rows with irrelevant data inadvertently while debugging. When I noticed the range of data for Pivot Table and last row number different I deleted the irrelevant data, ran macro which updated the lastrow correctly and the filtered the date correctly.

In the existing worksheet, the dates in the source worksheet are in date data type and the currep_date variable is set in date data type. I have been scratching my head for more than I did for before and researching articles in many websites to identify the error but I am still clueless.

I appreciate for your time and sharing your valuable knowledge and request for any alternative solution to filter dates without changing the date datatype. When I changed the date to "General" NumberFormat it worked.

Kind regards,

Roshan Shakya


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1295865-pi-visible-type-mismatch.html#post5226436

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...