pi>Visible <Type Mismatch>

[interface]

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"
Worksheets("Summary").UsedRange
Worksheets("PivotTable").Activate
ActiveSheet.Cells.ClearContents
ActiveSheet.Cells.ClearFormats
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"
ActiveSheet.UsedRange
ActiveSheet.Range("B:F").Select
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#
ActiveSheet.Range("A1").Select
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

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...865-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.

 

[interface]

thank you Fluff for your advice. I had posted this question in other forums too which as under:

- https://www.excelforum.com/excel-pr...865-pi-visible-type-mismatch.html#post5226489
- https://stackoverflow.com/questions/58784364/pi-visible-type-mismatch-on-excel-2010

I admit my mistake and will abide by the MrExcel's forum rules.

Kind regards,
Roshan Shakya