Error Code '1004' when attempting to filter pivot table

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I have a vba code that inserts a pivot table and in a section it willfilter the data by a specific field. My issue now is what if in the data thatspecific field is not available. How can I write in vba code if “Watchlist” isnot an option then leave the pivot table blank?

This is the piece of the code where it errors out if the data does nothave “Watchlist” as an option. Therefore, if “Watchlist” Is not an option, thenleave the pivot table blank and move on to the rest of the code.
Code:
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type").CurrentPage_
        = "Watchlist"

This is the full code:
Code:
Option Explicit
Sub Watchlist()
'
'Watchlist Macro
'Created by Miriam Hamid on 5/29/18
'Updated by Miriam Hamid on 7/11/18
 
'Clear Prior Data
Dim lRow2 As Long
 
lRow2 = Range("AY" & Rows.Count).End(xlUp).Row
    
   Columns("BF:BX").Select
    Selection.DeleteShift:=xlToLeft
    Selection.InsertShift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("AY5:BC"& lRow2 + 1).Select
    Selection.Clear
 
'Declare Variables to hold source cell range address
Dim mySourceData As String
 
'Declare Variables to hold references to source and destinationworksheets and Pivot Table
Dim SourceSheet As Worksheet
Dim DestSheet As Worksheet
 
'Declare Variales to hold row and column numbers that will definesource data cell range
Dim FirstRow As Long
Dim lastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
 
'Set/Define Source and Destination Variables
With ThisWorkbook
    Set SourceSheet =.Worksheets("Audit_Plan")
    Set DestSheet =.Worksheets("OTRC MOR File")
End With
 
'identify first row and first column of source data cell range
FirstRow = 6
FirstCol = 1
 
'Find last row and last column of source data cell range
'And obtain address of source data cell range
With SourceSheet.Cells
    lastRow =.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart,SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastCol =.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart,SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    mySourceData =.Range(.Cells(FirstRow, FirstCol), .Cells(lastRow,LastCol)).Address(ReferenceStyle:=xlR1C1)
End With
 
'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,SourceData:= _
        SourceSheet.Name &"!" & mySourceData, Version:=6).CreatePivotTable TableDestination_
        :=DestSheet.Cells(4, 58),TableName:="PivotTable1", DefaultVersion:=6
    
'Insert Watch List Pivot Data
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")
        .Orientation =xlPageField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type")
        .Orientation =xlPageField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To")
        .Orientation = xlRowField
        .Position = 1
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business")
        .Orientation = xlRowField
        .Position = 2
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Observations")
        .Orientation = xlRowField
        .Position = 4
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region")
        .Orientation = xlRowField
        .Position = 5
    End With
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership")
        .Orientation = xlRowField
        .Position = 6
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
   ActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Observations").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Observations")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
   ActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
    Range("BF4").Select
   ActiveSheet.PivotTables("PivotTable1").RowAxisLayoutxlTabularRow
   Columns("BF:BF").ColumnWidth = 25
   Columns("BH:BH").Select
    Selection.ColumnWidth = 25
   ActiveSheet.PivotTables("PivotTable1").PivotSelect"Observations[All]", _
        xlLabelOnly, True
   Columns("BI:BI").Select
    Selection.ColumnWidth = 25
   Range("BA11:BQ11").Select
   Range("BQ11").Activate
   ActiveSheet.PivotTables("PivotTable1").PivotSelect _
        "ASPAC 'Anne-MareeTassell' 'Multi O&T Businesses' 'A18338-APRA Prudential Standard CPS 232Business Continuity Management - Australia' Observations['(blank)']" _
        , xlDataAndLabel, True
   Range("BK16").Select
   ActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area").CurrentPage= _
        "(All)"
    WithActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")
       .PivotItems("Business").Visible = False
    End With
   ActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")._
        EnableMultiplePageItems =True
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type")._
        ClearAllFilters
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type").CurrentPage_
        = "Watchlist"
 
 
'Add Prior Month Date
    Range("BN1").Select
        With Selection
            .FormulaR1C1 ="=EOMONTH(TODAY(),-2)-DAY(EOMONTH(TODAY(),-2))+1"
            .NumberFormat ="mm/dd/yyyy"
            .Interior.Pattern =xlSolid
           .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color =65535
           .Interior.TintAndShade = 0
           .Interior.PatternTintAndShade = 0
        End With
        
    Range("BN1").Copy
    Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode =False
 
'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
       ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        SourceSheet.Name &"!" & mySourceData, Version:=6).CreatePivotTable TableDestination_
        :=DestSheet.Cells(4, 67),TableName:="PivotTable2", DefaultVersion:=6
        
'Insert Month End and After Insufficient/Limited Assurance Audits PivotData
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Report_To")
        .Orientation = xlRowField
        .Position = 1
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_To").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Business_Impacted")
        .Orientation = xlRowField
        .Position = 2
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Business_Impacted").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Audit_Name")
        .Orientation = xlRowField
        .Position = 3
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Audit_Name").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Observations")
        .Orientation = xlRowField
        .Position = 4
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Observations").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Region_Revised")
        .Orientation = xlRowField
        .Position = 5
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Region_Revised").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields( _
       "Report_Publication_Date")
        .Orientation = xlRowField
        .Position = 6
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
        Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
        False, False)
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Entity_Ownership")
        .Orientation = xlRowField
        .Position = 7
    End With
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating")
        .Orientation = xlRowField
        .Position = 8
    End With
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Total_Issues")
        .Orientation = xlRowField
        .Position = 9
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Published_Report_Name"_
        )
        .Orientation = xlRowField
        .Position = 10
    End With
   ActiveSheet.PivotTables("PivotTable2").RowAxisLayoutxlTabularRow
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Entity_Ownership")._
        Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
        False, False)
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating").Subtotals_
        = Array(False, False,False, False, False, False, False, False, False, False, False, False _
        )
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Total_Issues").Subtotals= _
        Array(False, False,False, False, False, False, False, False, False, False, False, False)
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Published_Report_Name")._
        Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
        False, False)
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")
        .Orientation =xlPageField
        .Position = 1
    End With
   Columns("BO:BO").Select
    Selection.ColumnWidth = 25
   Columns("BX:BX").Select
    Selection.ColumnWidth = 25
    Range("BM14:CB19").Select
   Range("CB19").Activate
   Columns("BT:BT").ColumnWidth = 14
   Columns("BU:BU").ColumnWidth = 11.38
   ActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area").CurrentPage= _
        "(All)"
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")
       .PivotItems("Business").Visible = False
    End With
   ActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")._
        EnableMultiplePageItems =True
   Columns("BP:BP").ColumnWidth = 15.25
    Range("BT8").Select
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
       PivotItems("3/21/2018").ShowDetail = False
   Columns("BT:BT").Select
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
        PivotFilters.Add2Type:=xlAfterOrEqualTo, Value1:=Range("BN1")
    Range("BV3").Select
    WithActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating")
        .PivotItems("NotRated").Visible = False
        .PivotItems("Roomfor Improvement").Visible = False
       .PivotItems("Sufficient Assurance").Visible = False
       .PivotItems("(blank)").Visible = False
    End With
   Columns("BV:BV").ColumnWidth = 19.38
   Columns("BW:BW").EntireColumn.AutoFit
    Columns("BV:BV").EntireColumn.AutoFit
   Columns("BU:BU").EntireColumn.AutoFit
   Columns("BS:BS").ColumnWidth = 13.5
   Columns("BQ:BQ").ColumnWidth = 19.38
 
    
'Copy Data from 1st PivotTable into predefined Table and copy untillast row
Dim lRow As Long
lRow = Range("BJ" & Rows.Count).End(xlUp).Row
 
    Sheets("OTRC MORFile").Select
   Range("AY5").FormulaR1C1 ="=IF(OR(RC[7]="""",RC[7]=""(blank)""),""TBD"",RC[7])"
   Range("AZ5").FormulaR1C1 = "=RC[7]&"" /""&RC[10]"
   Range("BA5").FormulaR1C1 = "=RC[7]"
   Range("BB5").FormulaR1C1 = "="" ""&CHAR(149)&""     ""&RC[7]"
   Range("BC5").FormulaR1C1 = "=RC[8]"
    
   Range("AY5:BB5").Copy Range("AY" & lRow)
    Application.CutCopyMode =False
    
'Copy Table Data and Paste Special Value
   Range("AY5:BC20").Copy
   Range("AY5:BC20").PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode =False
 
'Copy Data from 2nd PivotTable into predefined Table and copy untillast row
Dim LR As Long
Dim lRow3 As Long
lRow3 = Range("BX" & Rows.Count).End(xlUp).Offset(1).Row
LR = Range("AY" & Rows.Count).End(xlUp).Row
 
    Range("AY" & LR+ 1 & ":AY" & lRow3).formula ="=IF(BO5=""(blank)"",""TBD"",BO5)"
    Range("AZ" & LR+ 1 & ":AZ" & lRow3).formula = "=BP5&"" /""&BS5"
    Range("BA" & LR+ 1 & ":BA" & lRow3).formula ="=IF(BX5=""(blank)"",BQ5,BX5)"
    Range("BB" & LR+ 1 & ":BB" & lRow3).formula = "=""  •    ""&""Published on""&TEXT(BT5,""mm/dd/yyyy"")&""with ""&BV5&"" rating and ""&BW5&"" issues:"""
    Range("BC" & LR+ 1 & ":BC" & lRow3).formula = "=BU5"
 
'Copy Table Data and Paste Special Value
   Range("AY5:BC20").Copy
   Range("AY5:BC20").PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode =False
 
'Macro to replace "(i*" with bullets
Dim Bullet As String
Dim Bullet2 As String
 
'Define Variables
Bullet = "            •     "
     
'Replace roman numerals with bullets
Range("BB:BB").Select
    Selection.ReplaceWhat:="(i*)", Replacement:=Bullet, LookAt:=xlPart, _
        SearchOrder:=xlByRows,MatchCase:=False
    Selection.ReplaceWhat:="(v*)", Replacement:=Bullet, LookAt:=xlPart, _
        SearchOrder:=xlByRows,MatchCase:=False
    Selection.ReplaceWhat:="(x*)", Replacement:=Bullet, LookAt:=xlPart, _
        SearchOrder:=xlByRows,MatchCase:=False
    Selection.ReplaceWhat:="(xv*)", Replacement:=Bullet, LookAt:=xlPart, _
        SearchOrder:=xlByRows,MatchCase:=False
        
'Sort Column BC
   Columns("AY:BC").Select
   ActiveWorkbook.Worksheets("OTRC MORFile").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("OTRC MOR File").Sort.SortFields.AddKey:=Range("BC5:BC20" _
        ),SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    WithActiveWorkbook.Worksheets("OTRC MOR File").Sort
        .SetRangeRange("BC5:BC20")
        .Header = xlNo
        .MatchCase = False
        .Orientation =xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
'Header Creation and Formatting
   Range("AY4").FormulaR1C1 = "Responsible Owner"
   Range("AZ4").FormulaR1C1 = "Business / Region"
   Range("BA4").FormulaR1C1 = "Review Name"
   Range("BB4").FormulaR1C1 = "Issue Topis  / Target Dates / Status"
   Range("AY4:BB4").Select
        With Selection.Font
            .Name ="Arial"
            .Size = 8
        End With
 
'Add Border
    Dim lastRow1 As Long
    lastRow1 = Cells(Rows.Count,"AY").End(xlUp).Row
    Call SetRangeBorder(Range("AX5:BB"& lastRow1))
 
'Change font
    Range("AX5:BB"& lastRow1).Select
        With Selection.Font
            .Size = 8
        End With
 
'Add Sequential Numbers
    Range("AX5:AX"& lastRow1).formula = "=IF(BC5=BC4,AX4+1,1)"
    Range("AX:AX").Copy
   Range("AX:AX").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode =False
    
'Add Sub-Header
    Dim last_row As Long, iRow AsLong
    Dim N As Long, i As Long
    last_row = Sheets("OTRCMOR File").Range("AY" & Rows.Count).End(xlUp).Row
        N = 2
       
        'compare the values incolumn BC and insert headings when values are different
        For i = 2 To last_row
            If Sheets("OTRCMOR File").Range("BC" & N) <> Sheets("OTRC MORFile").Range("BC" & N + 1) Then
                Sheets("OTRCMOR File").Range("AH40:AM40").Copy
                Sheets("OTRCMOR File").Range("AX" & N + 1).Insert Shift:=xlDown
                N = N + 2
            Else:
                N = N + 1
            End If
        Next
    Application.CutCopyMode =False
 
'Select Merged cells in column AX and add header based on EntityOwnership (column BC)
    Dim R As Range, Adr As String
    With Application
        .FindFormat.Clear
        .ReplaceFormat.Clear
        .FindFormat.MergeCells =True
    End With
    Set R =Range("AX:AX").Find("", SearchFormat:=True)
    If Not R Is Nothing Then
        Adr = R.Address
        Do
            R.FormulaR1C1 ="=R[1]C[5]"
            R.Value = R.Value
            Set R =Range("AX:AX").Find("", R, SearchFormat:=True)
            If R Is Nothing ThenExit Do
            If R.Address = AdrThen Exit Do
            R.FormulaR1C1 ="=R[1]C[5]"
            R.Value = R.Value
            If R.Value = 0 Then
                R.UnMerge
            End If
        Loop
    Else
        MsgBox "no mergedcells in col AX"
        Exit Sub
    End If
    With Application
        .FindFormat.Clear
        .ReplaceFormat.Clear
    End With
 
'Find 0 value in column AX and clear cell with adjacent cells
    Dim r1 As Long
    Dim LastR As Long
    LastR = Range("B"& Rows.Count).End(xlUp).Row
    For r1 = 1 To LastR
        If Cells(r1,"AX") = 0 Then
            Range(Cells(r1,"AX"), Cells(r1, "BB")).Clear
        End If
    Next r1
 
'Find and replace "Area" and "Non-O&T"
   Columns("AX:AX").Select
    Selection.ReplaceWhat:="Area", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.ReplaceWhat:="Non-O&T", Replacement:="Shared", LookAt:=xlPart_
        , SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
'Count Notation
   Range("AZ2").formula = "=COUNT(AX:AX) &""Audits  on the Watch List(""&COUNTIF(BC:BC,""O&TArea"")&"" Owned,""&COUNTIF(BC:BC,""Non-O&T"")&""Shared)"""
    Range("AZ2").Select
        With Selection.Font
            .Name ="Arial"
            .Size = 11
        End With
    Range("AZ2").Copy
    Range("AZ2").PasteSpecialPaste:=xlPasteValues
    Application.CutCopyMode =False
 
End Sub
 
Sub SetRangeBorder(poRng As Range)
    If Not poRng Is Nothing Then
       poRng.Borders(xlDiagonalDown).LineStyle = xlNone
       poRng.Borders(xlDiagonalUp).LineStyle = xlNone
       poRng.Borders(xlEdgeLeft).LineStyle = xlContinuous
       poRng.Borders(xlEdgeTop).LineStyle = xlContinuous
        poRng.Borders(xlEdgeBottom).LineStyle= xlContinuous
       poRng.Borders(xlEdgeRight).LineStyle = xlContinuous
       poRng.Borders(xlInsideVertical).LineStyle = xlContinuous
       poRng.Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End If
End Sub

Thank you
 
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top