Sub calculatedFields()
Dim rowCount As Integer
Dim pTable As PivotTable 'https://www.wallstreetmojo.com/vba-pivot-table/
Dim pCache As PivotCache
Dim pRange As Range
Dim pSheet As Worksheet
Dim pivotSource As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Cells(1, 17) = "Pulled Within Free Time"
'get number of rows'
rowCount = WorksheetFunction.CountA(Columns("A:A"))
'on error move to next row
'added this because unrealistic dates like 01/01/0001 causes an error in Excel
On Error Resume Next
For i = 2 To rowCount
'if Pull Out Pier is Empty, Pull Out Pier = Today
If IsEmpty(Cells(i, 3)) = True Then
Cells(i, 3) = Date
End If
'If Start of Storage is Empty, status is "Start of Storage is Empty"
If IsEmpty(Cells(i, 2)) = True Then
Cells(i, 17) = "Start of Storage is Empty"
Else
'Subtract Pull Out Pier from Start of Storage
Cells(i, 17) = Cells(i, 3) - Cells(i, 2)
End If
'If difference is less than 1 then its "Pulled Within Free Time"
If Cells(i, 17) <= 0 Then
Cells(i, 17) = "Pulled Within Free Time"
End If
Cells(1, 18) = "Discharge Month"
If IsEmpty(Cells(i, 4)) = True Then
Cells(i, 18) = "No Discharge Date"
Else
Cells(i, 18) = Month(Cells(i, 4))
End If
Next
'rename active worksheet as "source"
ActiveSheet.Name = "pivotSource"
Worksheets.Add After:=ActiveSheet 'add new worksheet
ActiveSheet.Name = "Pivot Sheet" 'rename added worksheet to "Pivot Sheet"
Set pSheet = Worksheets("Pivot Sheet")
Set pivotSource = Worksheets("pivotSource")
lastRow = pivotSource.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = pivotSource.Cells(1, Columns.Count).End(xlToLeft).Column
Set pRange = pivotSource.Cells(1, 1).Resize(lastRow, lastCol)
Set pCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pRange)
Set pTable = pCache.CreatePivotTable(TableDestination:=pSheet.Cells(1, 1), TableName:="VolumeContainer")
'recording of pivot table with name changed to "VolumeContainer"
With ActiveSheet.PivotTables("VolumeContainer").PivotFields("CoCd")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("VolumeContainer").PivotFields("Discharge_")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("VolumeContainer").PivotFields("Discharge Month")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("VolumeContainer").AddDataField ActiveSheet.PivotTables( _
"VolumeContainer").PivotFields("Container Number"), "Count of Container Number", _
xlCount
End Sub
