Code to create pivot if pivot item is available in data source.

Hi All,
I have created macro which creates two pivot tables.
Row fields are same for both the pivot tables. Pt Cache is same for both the tables. Only change is in column field I apply filter for only one pivot item.
In one table, pivot item selected is "Hours" and rest all are made not visible. In second table,pivot item made visible is,"Hour" and other are made invisible.
But even if when pivot item ,"hour" is not in data source, it still creates table for ,"Hours". Basically it creates two pivots for , "Hours" pivot item.
How can I fix this one?
Requirement is if pivot item is not available in data source it should create blank pivot.
Code however works perfectly if both ," Hours" and ," Hour" are available in data source and it creates perfect two separate correct pivot tables.

Please help.
Regards,
Ishwar.

Here is the code that i am using.
Sub PAXYZ_Hours_Mgr()
Worksheets.Add().Name = "Mfg Hours Pivot"

Dim WSD As Worksheet
Dim LastRow As Long, LastCol As Long
Dim PRange As Range
Set WSD = Worksheets("Standard")
Sheets("Standard").Select
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
LastCol = WSD.Cells(3, Application.Columns.Count).End(xlToLeft).Column

Set PRange = WSD.Cells(3, 1).Resize(LastRow - 2, LastCol)
PRange.Name = "MyRange"

Dim PTCahce As PivotCache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Standard!MyRange")


Dim PT1 As PivotTable

Dim PTOutput1 As Worksheet

Set PTOutput1 = Worksheets("Mfg Hours Pivot")



Set PT1 = PTCache.CreatePivotTable(TableDestination:=PTOutput1.Cells(1, 1), _
TableName:="Mfg Hours Pivot")

With PT1
.PivotFields("Expenditure Organization Name").Orientation = xlRowField
.PivotFields("Expenditure Organization Name").Position = 1
.PivotFields("Employee Name").Orientation = xlRowField
.PivotFields("Employee Name").Position = 2
.PivotFields("Expenditure Item Date").Orientation = xlColumnField
.PivotFields("Expenditure Item Date").Position = 1
.PivotFields("Unit Of Measure M").Orientation = xlColumnField
.PivotFields("Unit Of Measure M").Position = 2

With PT1.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
'number formating od datafiled
.NumberFormat = "#,##0.00"

With PT1.PivotFields("Unit Of Measure M")
On Error Resume Next
.PivotItems("Currency").Visible = False
.PivotItems("Each").Visible = False
.PivotItems("Foot").Visible = False
.PivotItems("Hours").Visible = False
.PivotItems("Inch").Visible = False
.PivotItems("Ounce").Visible = False
.PivotItems("Pound").Visible = False
.PivotItems("Square Feet").Visible = False
On Error GoTo 0

End With
End With
End With



With PT1
.PivotFields("Employee Name").Subtotals(1) = True
.PivotFields("Employee Name").Subtotals(1) = False
End With
'CODE TO MAKE SUBTOTAL EXP ORG NAME TRUE
With PT1
.PivotFields("Expenditure Organization Name").Subtotals(1) = True
End With

'Dim fDate As Date

'set range of dates to be grouped
Set rngGroup1 = PT1.PivotFields("Expenditure Item Date").DataRange

'determine the first date in the PivotField of Dates
PT1.PivotFields("Expenditure Item Date").AutoSort Order:=xlDescending, Field:="Expenditure Item Date"
'fDate = PvtTbl.PivotFields("Expenditure Item Date").DataRange.Cells(1).Value
rngGroup1.Cells(1).Group Periods:=Array(False, False, False, False, True, False, True)

'to make pivot table in tabular form

PTOutput1.PivotTables("Mfg Hours Pivot").RowAxisLayout xlTabularRow

'to format pivot table
Sheets("Mfg Hours Pivot").Select
ActiveSheet.PivotTables("Mfg Hours Pivot").TableStyle2 = "PivotStyleLight15"

With PT1
.PivotFields("Expenditure Item Date") _
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
.PivotFields("Years").Subtotals = _
Array(True, False, False, False, False, False, False, False, False, False, False, False)

End With

With PT1
.PivotFields("Years").AutoSort Order:=xlDescending, Field:="Years"
End With


Sheets("Mfg Hours Pivot").Select
Columns("A:BZ").EntireColumn.AutoFit


ActiveSheet.PivotTables("Mfg Hours Pivot").PivotSelect "", xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With


Sheets("Mfg Hours Pivot").Select
ActiveSheet.PivotTables("Mfg Hours Pivot").PivotSelect _
"'Expenditure Organization Name'[All;Total]", xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With


Dim lRow2 As Long
lRow2 = Cells(Rows.Count, 1).End(xlUp).Row
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 18.71
Range("A4").Select
ActiveCell.FormulaR1C1 = "Grand Total Hour"
Range("B4").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(1000000,R)"
Range("A5").Select
Selection.AutoFill Destination:=Range("A5:A" & lRow2)
'code to format cloumn, "A", as column,"B"
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit
'code to highlight grandtotal with double underline

Range("A5:A" & lRow2).Select
Cells.FormatConditions.Delete
Range("A5:A" & lRow2).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=B5=""Grand Total"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Underline = xlUnderlineStyleDouble
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = True
'code to cut width of column B, C, E and F
Columns("E:E").ColumnWidth = 10.86
Columns("F:F").ColumnWidth = 11.14
Columns("B:B").ColumnWidth = 45
Columns("C:C").ColumnWidth = 25

Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Comm
Range("A5").Select
ActiveWindow.FreezePanes = True

Range("A4").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Columns("A:A").EntireColumn.AutoFit

Worksheets.Add().Name = "Eng Hours Pivot"
Dim PT As PivotTable
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("Eng Hours Pivot")
Set PT = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="Eng Hours Pivot")


With PT
.PivotFields("Expenditure Organization Name").Orientation = xlRowField
.PivotFields("Expenditure Organization Name").Position = 1
.PivotFields("Employee Name").Orientation = xlRowField
.PivotFields("Employee Name").Position = 2
.PivotFields("Expenditure Item Date").Orientation = xlColumnField
.PivotFields("Expenditure Item Date").Position = 1
.PivotFields("Unit Of Measure M").Orientation = xlColumnField
.PivotFields("Unit Of Measure M").Position = 2

With PT.PivotFields("Quantity")
.Orientation = xlDataField
.Function = xlSum
'number formating od datafiled
.NumberFormat = "#,##0.00"
End With
End With

With PT.PivotFields("Unit Of Measure M")
On Error Resume Next
.PivotItems("Currency").Visible = False
.PivotItems("Each").Visible = False
.PivotItems("Foot").Visible = False
.PivotItems("Hour").Visible = False
.PivotItems("Inch").Visible = False
.PivotItems("Ounce").Visible = False
.PivotItems("Pound").Visible = False
.PivotItems("Square Feet").Visible = False
On Error GoTo 0


End With




With PT
.PivotFields("Employee Name").Subtotals(1) = True
.PivotFields("Employee Name").Subtotals(1) = False
End With

With PT
.PivotFields("Expenditure Organization Name").Subtotals(1) = True
End With




Set rngGroup2 = PT.PivotFields("Expenditure Item Date").DataRange


PT.PivotFields("Expenditure Item Date").AutoSort Order:=xlDescending, Field:="Expenditure Item Date"

rngGroup2.Cells(1).Group Periods:=Array(False, False, False, False, True, False, True)


PTOutput.PivotTables("Eng Hours Pivot").RowAxisLayout xlTabularRow


Sheets("Eng Hours Pivot").Select
ActiveSheet.PivotTables("Eng Hours Pivot").TableStyle2 = "PivotStyleLight15"
With PT
.PivotFields("Expenditure Item Date") _
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
False, False)
.PivotFields("Years").Subtotals = _
Array(True, False, False, False, False, False, False, False, False, False, False, False)
End With
With PT
.PivotFields("Years").AutoSort Order:=xlDescending, Field:="Years"
End With
Sheets("Eng Hours Pivot").Select
Columns("A:BZ").EntireColumn.AutoFit
ActiveSheet.PivotTables("Eng Hours Pivot").PivotSelect "", xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
End With

Sheets("Eng Hours Pivot").Select
ActiveSheet.PivotTables("Eng Hours Pivot").PivotSelect _
"'Expenditure Organization Name'[All;Total]", xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End Wit
Dim lRow3 As Long
lRow3 = Cells(Rows.Count, 1).End(xlUp).Row
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 18.71
Range("A4").Select
ActiveCell.FormulaR1C1 = "Grand Total Hour"
Range("B4").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A5").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(1000000,R)"
Range("A5").Select
Selection.AutoFill Destination:=Range("A5:A" & lRow3)
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:A").EntireColumn.AutoFit
Range("A5:A" & lRow2).Select
Cells.FormatConditions.Delete
Range("A5:A" & lRow2).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=B5=""Grand Total"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Underline = xlUnderlineStyleDouble
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = True
Columns("E:E").ColumnWidth = 10.86
Columns("F:F").ColumnWidth = 11.14
Columns("B:B").ColumnWidth = 45
Columns("C:C").ColumnWidth = 25
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Comma"
Range("A5").Select
ActiveWindow.FreezePanes = True
Range("A4").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Columns("A:A").EntireColumn.AutoFit

Sheets("Mfg Hours Pivot").Select
With PT1
.PivotFields("Years").AutoSort Order:=xlDescending, Field:="Years"
End With
With PT1
.PivotFields("Years").Subtotals = _
Array(True, False, False, False, False, False, False, False, False, False, False, False)
End With
Sheets("Mfg Hours Pivot").Select
Columns("E:E").ColumnWidth = 10.86
Columns("F:F").ColumnWidth = 11.14
Columns("B:B").ColumnWidth = 45
Columns("C:C").ColumnWidth = 25
Columns("D:D").ColumnWidth = 10
Application.DisplayAlerts = True
End Sub