VBA筛选数据透视表报错求助：无法获取WorksheetFunction的Large属性

Let's break down your problems and fix them step by step:

1. Root Cause of the Large Function Error #

Looking at your code, when calculating Second_Highest_Max, you used .Range("A:A") inside the With Worksheets("PIVOT") block. That means you're trying to pull dates from the PIVOT worksheet's column A instead of the HISTORICALS sheet where your filtered raw data lives. This mismatch is why the Large function throws an error—it's not finding the expected date values in PIVOT's A column.

2. Correcting the Date Retrieval Code #

First, fix the reference to the HISTORICALS sheet for both date calculations. Also, it's better to avoid formatting dates immediately (keep them as date values instead of strings) to prevent issues with pivot table filtering later:

Dim wsHist As Worksheet
Dim highestDate As Date
Dim secondHighestDate As Date
Dim countOfTopDate As Long

Set wsHist = ThisWorkbook.Worksheets("HISTORICALS")

' Get top two unique dates from HISTORICALS column A
highestDate = WorksheetFunction.Large(wsHist.Range("A:A"), 1)
countOfTopDate = WorksheetFunction.CountIf(wsHist.Range("A:A"), highestDate)
secondHighestDate = WorksheetFunction.Large(wsHist.Range("A:A"), countOfTopDate + 1)

' Optional: Print formatted dates to Immediate Window for verification
Debug.Print Format(highestDate, "Short Date"), Format(secondHighestDate, "Short Date")

3. Fixing the Pivot Table Filter #

Your hunch was right—xlValueEquals is not the correct filter type for a pivot field that's a row/column/page label (like your "ipg:date" field). That filter type is meant for value fields (the numeric values in the pivot table), not for categorical/date labels.

Instead, use one of these reliable methods to filter the pivot table for your two dates:

Method 1: Use VisibleItemsList (Cleanest Approach) #

This method directly sets the list of visible date items using their string representations (pivot table date items are stored as strings in Excel's internal format):

Dim wsPivot As Worksheet
Dim pt As PivotTable
Dim dateField As PivotField

Set wsPivot = ThisWorkbook.Worksheets("PIVOT")
Set pt = wsPivot.PivotTables("PivotTable1")
Set dateField = pt.PivotFields("ipg:date")

' Clear existing filters
dateField.ClearAllFilters

' Set visible items to the two dates (convert dates to pivot-compatible strings)
dateField.VisibleItemsList = Array( _
    CStr(highestDate), _
    CStr(secondHighestDate) _
)

Method 2: Loop Through PivotItems to Set Visibility #

If you need more control (e.g., handling date formatting inconsistencies), loop through each pivot item and set its visibility:

' Clear existing filters first
dateField.ClearAllFilters

' Loop through each date item in the pivot field
Dim pi As PivotItem
For Each pi In dateField.PivotItems
    ' Check if the item's date matches either of our target dates
    pi.Visible = (CDate(pi.Value) = highestDate) Or (CDate(pi.Value) = secondHighestDate)
Next pi

4. Full Corrected Code #

Putting it all together, here's the complete working subroutine:

Sub Select_Last_Two_Days()
    Dim wsHist As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim dateField As PivotField
    Dim highestDate As Date
    Dim secondHighestDate As Date
    Dim countOfTopDate As Long
    
    ' Set worksheet references
    Set wsHist = ThisWorkbook.Worksheets("HISTORICALS")
    Set wsPivot = ThisWorkbook.Worksheets("PIVOT")
    Set pt = wsPivot.PivotTables("PivotTable1")
    Set dateField = pt.PivotFields("ipg:date")
    
    ' Retrieve top two unique dates from HISTORICALS
    highestDate = WorksheetFunction.Large(wsHist.Range("A:A"), 1)
    countOfTopDate = WorksheetFunction.CountIf(wsHist.Range("A:A"), highestDate)
    secondHighestDate = WorksheetFunction.Large(wsHist.Range("A:A"), countOfTopDate + 1)
    
    ' Verify dates in Immediate Window (Ctrl+G to view)
    Debug.Print "Top Date: " & Format(highestDate, "Short Date")
    Debug.Print "Second Top Date: " & Format(secondHighestDate, "Short Date")
    
    ' Filter pivot table for the two dates
    dateField.ClearAllFilters
    dateField.VisibleItemsList = Array(CStr(highestDate), CStr(secondHighestDate))
End Sub

Key Notes #

• Always use explicit worksheet references (instead of relying on With blocks alone) to avoid accidental reference mismatches.
• Keep dates as date values (not formatted strings) until you need to display them—this prevents issues with pivot table item matching.
• For pivot label fields (like dates), use VisibleItemsList or loop through PivotItems instead of value-based filters.

内容的提问来源于stack exchange，提问作者calicationoflife