Hi All,
I'm new to this forum, but not too new as a follower. I've started learning VBA for my own sake and tips and tricks that I've seen in your forum was actually too helpful.
This time I need a help. I've a work sheet containing base data and the other sheet with a Pivot created based on the Base data. Here, I've created one Macro which is generating the Pivot. Now the Problem is that there are few fileds which needs filters to be placed. As for example one Date Filter which will take the date from another sheet from the same workbook and will populate the Pivot as "after or equal to" the date provided, let's say in Sheet3, Cell B2. And, one more filter is needed at the 1st Field, which will select only 10 out of some 80 odd values, this I've tried creating an array and tried to call the Values in a List given in a seperate Sheet from the same workbook, unfortunately the code is throwing some error, which I'm sure because of my poor knowledge in VBA (but surely improving). The Code I've highlighted with Red/Bold is actually throwing the error which says, "Run Time Error '1004'. AutoFilter method of Range Class Failer".
The codes are given below for your ref:
I'm new to this forum, but not too new as a follower. I've started learning VBA for my own sake and tips and tricks that I've seen in your forum was actually too helpful.
This time I need a help. I've a work sheet containing base data and the other sheet with a Pivot created based on the Base data. Here, I've created one Macro which is generating the Pivot. Now the Problem is that there are few fileds which needs filters to be placed. As for example one Date Filter which will take the date from another sheet from the same workbook and will populate the Pivot as "after or equal to" the date provided, let's say in Sheet3, Cell B2. And, one more filter is needed at the 1st Field, which will select only 10 out of some 80 odd values, this I've tried creating an array and tried to call the Values in a List given in a seperate Sheet from the same workbook, unfortunately the code is throwing some error, which I'm sure because of my poor knowledge in VBA (but surely improving). The Code I've highlighted with Red/Bold is actually throwing the error which says, "Run Time Error '1004'. AutoFilter method of Range Class Failer".
The codes are given below for your ref:
Code:
Sub MakePivotTable_Tab1_Ovrdue_Qual()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Sheet1")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Sheet2")
Dim PTCache As PivotCache
Dim PRange As Range
Dim PI As PivotItem
Dim Pf As PivotField
Dim Arr As Variant
Dim i As Integer
' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(7, 1), TableName:="SamplePivot")
' Define the layout of the pivot table
' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True
' Set up the Pivot fields
With pt
.PivotFields("Process Area").Orientation = xlRowField
.PivotFields("Unique Role ID").Orientation = xlRowField
.PivotFields("Projects Names").Orientation = xlRowField
.PivotFields("Role and Sub Role").Orientation = xlRowField
.PivotFields("Employment Type").Orientation = xlRowField
.PivotFields("Requested Name").Orientation = xlRowField
.PivotFields("Location Role").Orientation = xlRowField
.PivotFields("Role Description").Orientation = xlRowField
.PivotFields("Project Description").Orientation = xlRowField
.PivotFields("Request Status").Orientation = xlRowField
.PivotFields("Resource Name").Orientation = xlRowField
.PivotFields("Booking Condition").Orientation = xlRowField
.PivotFields("Request Manager").Orientation = xlRowField
.PivotFields("Task Start").Orientation = xlRowField
.PivotFields("Task Finish").Orientation = xlRowField
.PivotFields("DOP Resource Status").Orientation = xlPageField
.PivotFields("Week Commencing").Orientation = xlColumnField
End With
'This sets each field in ascending order. It applies this even to fields
' that are not currently in the PivotTable.
For Each Pf In pt.PivotFields
Pf.AutoSort xlAscending, Pf.Name
Pf.Subtotals(1) = True
Pf.Subtotals(1) = False
Next Pf
'This changes the formatting of any field that appears in the Values area
For Each Pf In pt.DataFields
Pf.Function = xlSum
Pf.NumberFormat = "0.0"
Next Pf
'This section applies Classic PivotTable settings
' and turns off the Contextual Tooltips and the Expand/Collapse buttons
With pt
.InGridDropZones = True
.RowAxisLayout xlTabularRow
.TableStyle2 = ""
.DisplayContextTooltips = False
.ShowDrillIndicators = False
End With
'This ensures that only data that still exists in the data
' that drives the PivotTable
' will appear in the PivotTable dropdown lists
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
'Applying Filter at "DOP Resource Status"
With pt.PivotFields("DOP Resource Status").CurrentPage = "(All)"
pt.PivotFields("DOP Resource Status").PivotItems("Other - please provide details in comments field").Visible = False
pt.PivotFields("DOP Resource Status").PivotItems("(blank)").Visible = False
pt.PivotFields("DOP Resource Status").EnableMultiplePageItems = True
End With
[B][COLOR=#ff0000]Arr = WorksheetFunction.Transpose(Worksheets("Lists").Range("I1:I3").Value)
For i = LBound(Arr) To UBound(Arr)
Arr(i) = CStr(Arr(i))
Next i
Worksheets("Sheet2").Range("A8:CA8" & LastRow).AutoFilter Field:=1, Criteria1:=Arr, Operator:=xlFilterValues
[/COLOR][/B]
' Setting up the data field
With pt.PivotFields("Assignment Work")
.Orientation = xlDataField
.Position = 1
.Caption = "Sum of Assignment Work"
.Function = xlSum
End With
' calc the pivot table
pt.ManualUpdate = False
End Sub
