[B]I am trying to create a pivot table and filter it based on the users input. They are trying to filter the period to one month. There could be twelve months of detail in the data. Attached is the code in question.


Please Login or Register  to view this content.
Private Sub PivotTablesUS_Click()

Dim DataSheet As Variant
Dim PivotSheet As String
Dim PT As PivotTable
Dim sShtNoPer As String
Dim rngData As Range
Dim rngOutput As Range
Dim prd As String
Const Color As Integer = 5

'******* Crate Period Pivot Table Tab*****************

sShtNoPer = "Period"

'************ Delete Period Tab if it exists already***********
On Error Resume Next
application.DisplayAlerts = False
ActiveWorkbook.Sheets(sShtNoPer).Delete
application.DisplayAlerts = True
On Error GoTo 0

'************** Add a new Worksheet and Rename it************
Worksheets.Add
ActiveSheet.Name = sShtNoPer
ActiveSheet.Tab.ColorIndex = Color

'*********** Create Pivot Cache******************
With Worksheets("Depreciation Accum Detail")
Set rngData = .Range("A1").CurrentRegion
End With
With Worksheets("Period")
Set rngOutput = .Range("A3").CurrentRegion
End With

With ActiveWorkbook.PivotCaches
With .Create(SourceType:=xlDatabase, SourceData:=rngData, Version:=xlPivotTableVersion10)
Set PT = .CreatePivotTable(tabledestination:=rngOutput, TableName:="Period", defaultversion:=xlPivotTableVersion10)
End With
End With

'*********** Create Pivot Fields***************

With PT.PivotFields("Account")
.Orientation = xlRowField
.Position = 1
End With
With PT.PivotFields("Acct Name")
.Orientation = xlRowField
.Position = 2
End With
With PT.PivotFields("Period")
.Orientation = xlRowField
.Position = 3
End With

With PT
.PivotFields("Amount").Orientation = xlDataField

'************Turn off subtotals on account and account descriptions**************
.PivotFields("Account").Subtotals(1) = False
.PivotFields("Acct Name").Subtotals(1) = False
.PivotFields("Period").Subtotals(1) = False

'************Format Total Column*****************
.PivotFields("Sum of Amount").NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
End With

'************* Show current period only from user input************This is what is not working

prd = application.InputBox(Prompt:="Please Enter the Current Period in MMM-YY format", Title:="Specify Period")

PT.PivotFields("Period").ClearAllFilters
PT.PivotFields("Period").PivotItems(prd).Visible = True

'*************Add title to Pivot Table**********************
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = _
"BP HBT US Accumulated Depreciation vs Depreciation Expense"
ActiveSheet.Range("A1").Select
selection.Font.Bold = True

Set PT = Nothing
Set rngData = Nothing
Set rngOutput = Nothing
ActiveWorkbook.ShowPivotTableFieldList = False

End Sub