The code is to create a pivot table for each ProdId on the data sheet.
If a value is not found in column "ProdId" I want to be able to skip the pivot table creation.
Code works fine when I don't use the On Error command but a pivot table gets created when ProdId="04" and no match is found.
If a value is not found in column "ProdId" I want to be able to skip the pivot table creation.
Code works fine when I don't use the On Error command but a pivot table gets created when ProdId="04" and no match is found.
VBA Code:
Sub FuelReport()
'FUEL REPORT FOR PERSONAL, RED AND WHITE FUEL
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
Dim FilterValue As String
Dim myPivotField As PivotField
Dim RSheet As String
Dim i As Integer
Dim PMonth As String
Dim Wb As Workbook
Dim Filename As String
Dim Path As String
Dim datetoday As String
Dim monthtoday As String
Dim ProdId As String
Dim num As Integer
ActiveSheet.Name = "Table"
For i = 1 To 5
If i = 1 Then
RSheet = "PERSONAL"
FilterValue = "01"
ElseIf i = 2 Then
RSheet = "GAS"
FilterValue = "01"
ElseIf i = 3 Then
RSheet = "RED"
FilterValue = "02"
ElseIf i = 4 Then
RSheet = "WHITE"
FilterValue = "03"
Else
RSheet = "DEF"
FilterValue = "04"
End If
'Define Data Range
Set DSheet = Worksheets("Table")
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Declare Variables
On Error Resume Next
Application.DisplayAlerts = False
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = RSheet
Set PSheet = Worksheets(RSheet)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:=RSheet)
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:=RSheet)
'Insert Row Fields
With ActiveSheet.PivotTables(RSheet).PivotFields("VehcName")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables(RSheet).PivotFields("DrvrName")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables(RSheet).PivotFields("Vehicle")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables(RSheet).PivotFields("Date")
.Orientation = xlRowField
.Position = 4
End With
'Insert Data Field
With ActiveSheet.PivotTables(RSheet).PivotFields("Quantity")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.NumberFormat = "#,##0.00"
.Name = "Revenue "
End With
'Insert Filter Fields
With ActiveSheet.PivotTables(RSheet).PivotFields("Prodid")
.Orientation = xlPageField
.Position = 1
End With
Set myPivotField = ActiveSheet.PivotTables(RSheet).PivotFields("Prodid")
myPivotField.CurrentPage = FilterValue
'Show only totals for each DrvrName
ActiveSheet.PivotTables(RSheet).PivotFields("VehcName").ShowDetail = False
'Format Pivot
TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
ActiveWorkbook.TableStyles.Add ("PivotTable Style 1")
ActiveSheet.PivotTables("White").TableStyle1 = "PivotTable Style 1"
With ActiveWorkbook.TableStyles("PivotTable Style 1").TableStyleElements( _
xlTotalRow).Font
.FontStyle = "Bold"
.TintAndShade = 0
.ThemeColor = xlThemeColorDark1
End With
With ActiveWorkbook.TableStyles("PivotTable Style 1").TableStyleElements( _
xlTotalRow).Interior
.Color = 192
.TintAndShade = 0
End With
ActiveSheet.PivotTables(RSheet).TableStyle2 = "PivotTable Style 1"
'Drill down personal fuel to date
If i = 1 Then
ActiveSheet.PivotTables("Personal").PivotSelect "VehcName[All]", xlLabelOnly + _
xlFirstRow, True
ActiveSheet.PivotTables("Personal").PivotFields("VehcName").DrillTo "Date"
Call PivotFilter
End If
'Insert Villeneuve Header for each sheet
Cells(1, 1).Activate
Rows("1:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Value = "Villeneuve Contstruction"
If i = 5 Then
Range("B2").Value = RSheet & " FLUID"
Else
Range("B2").Value = RSheet & " FUEL"
End If
Range("B3").NumberFormat = ("mmmm yyyy")
datetoday = MonthName(Month(DateAdd("m", -1, Date))) & " " & Year(Date)
Range("B3").Value = datetoday
Cells("B3").Select
Range("B1:C1").Merge
Range("B1:C1").HorizontalAlignment = xlCenter
Range("B2:C2").Merge
Range("B2:C2").HorizontalAlignment = xlCenter
Range("B3:C3").Merge
Range("B3:C3").HorizontalAlignment = xlCenter
Range("B1:C3").BorderAround _
LineStyle:=xlContinuous, Weight:=xlMedium
Range("B1:C3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16777024
.TintAndShade = 0
End With
Selection.Font.Bold = True
Range("B1:C1").Select
Selection.Font.Size = 26
Range("B2:C2").Select
Selection.Font.Size = 18
Range("B3:C3").Select
Selection.Font.Size = 12
Columns("B:C").Select
Range("B4:C4").Activate
Selection.ColumnWidth = 30
ActiveSheet.PivotTables(RSheet).PivotSelect "VehcName[All]", xlLabelOnly + _
xlFirstRow, True
On Error GoTo Finish:
ProdId = Application.Match("04", Range(Cells(2, 9), Cells(21, 9)), 0)
If Not IsError(ProdId) Then
i = i
GoTo Finished
Else
End If
Finish:
i = 5
Finished:
Next i
End Sub