Excel VBA - Entering Filter into Pivot Table returning Object Required

Question

I have some VBA code which is set up to look up a dynamic (changing) table of data, the code then changes the range to fit into pivot tables, and refreshes the pivot tables based on the new range.

The code works until it reaches the part where it needs to apply a filter. I have a date value which I have declared under a String called PivotFilter, when the code goes to apply the filter, i get a Run Time error 1004 - Application defined or Object defined error

I have been trying to figure this out for the last two hours, and cannot seem to fix it, I have tried to change the string to a range, this also didn't work. Any suggestions?

EDIT Fixed the typo in the erroneous section, and i'm still getting application defined or object defined error

Sub PortfolioDataLoad()

Dim wb1 As Workbook
Dim ws1, ws2, ws3, ws4, ws5 As Worksheet
Dim r1, r2, r3 As Range
Dim StartPoint, DataRange As Range
Dim PivotName, Pivotname2, Pivotname3, Pivotname4 As String
Dim Datefrom, Dateto As Range
Dim PivotFilter As String


PivotFilter = Worksheets("Configuration").Range("B1")

PivotName = "PivotTable3"
Pivotname2 = "PivotTable4"
Pivotname3 = "PivotTable5"
Pivotname4 = "PivotTable1"

Application.StatusBar = "Transforming data into report graphs..."

'Set Variables Here

Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("Control Sheet")
Set ws2 = wb1.Sheets("Program Data")
Set ws3 = wb1.Sheets("Configuration")
Set ws4 = wb1.Sheets("Overview")


If ws1.Visible = False Then
   ws1.Visible = True
End If

'Dynamically Retrieve Data from Program Data
Set StartPoint = ws2.Range("A1")
Set DataRange = ws2.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
NewRange = ws2.Name & "!" & _
       DataRange.Address(ReferenceStyle:=xlR1C1)

If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then
    MsgBox "One of your data columns in the 'Program Data' tab has a blank heading." & vbNewLine _
      & "Please fix and re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
 End If

'Change Pivot Range to Cache set above
ws3.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

ws3.PivotTables(Pivotname2).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

ws3.PivotTables(Pivotname3).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)

ws3.PivotTables(Pivotname4).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)


'Refresh Tables
ws3.PivotTables(PivotName).RefreshTable
ws3.PivotTables(Pivotname2).RefreshTable
ws3.PivotTables(Pivotname3).RefreshTable
ws3.PivotTables(Pivotname4).RefreshTable


'Set Date in Pivot Table Filter - Results in Run-time error '424': Object Required

ws3.PivotTables(PivotName).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

ws3.PivotTables(Pivotname2).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

ws3.PivotTables(Pivotname4).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

End Sub

Steve S Steve S · Accepted Answer · 2014-10-27T05:04:27

I think you've got a typo where w3 should be ws3. I believe the lines

w3.PivotTables(PivotName).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

w3.PivotTables(Pivotname2).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

w3.PivotTables(Pivotname4).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

should be

ws3.PivotTables(PivotName).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

ws3.PivotTables(Pivotname2).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

ws3.PivotTables(Pivotname4).PivotFields("Planning Month").PivotFilters.Add _
Type:=xlBeforeOrEqualTo, Value1:=PivotFilter

Also, note that NewRange wasn't defined.

Using Option Explicit can help find these. You can read more about it here.

Be aware that when you define multiple items on a single line, only the last variable is actually defined with the type specified. For example:

Dim PivotName, Pivotname2, Pivotname3, Pivotname4 As String

results in variable Pivotname4 being created as String but variables PivotName, Pivotname2 and Pivotname3 created as Variant.

You're assigning String values to PivotName, Pivotname2 and Pivotname3 so if you look at the item type while debugging, you'll see they are Variant/String. It seems that it should be okay, but it could be an issue.

You could try altering your Dim statments to explicitly set the variable type to String as I think you intended. For example:

Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim Pivotname2 As String
Dim Pivotname3 As String
Dim Pivotname4 As String
Dim Datefrom As Range
Dim Dateto As Range
Dim PivotFilter As String
Dim NewRange As Range

One more thing: Does the PivotField named "Planning Month" exist in the PivotTables? You may need to add it if it doesn't exist before you can apply the filter to it.

Excel VBA - Entering Filter into Pivot Table returning Object Required

3 Answers