Sub createPivotTableFromRange()
' Delivery Location Pivot Table
Dim rng As Range
Dim sht As Worksheet
Dim pTable As PivotTable
Set rng = ActiveSheet.Cells(1, 1).CurrentRegion
Set sht = ActiveWorkbook.Worksheets.Add
sht.Name = "Delivery Pivot"
Sheets("Data").Select
Set pTable = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng.Address, Version:=8).CreatePivotTable(TableDestination:= _
sht.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
With pTable
With .PivotFields("Position Status")
.Orientation = xlPageField
.CurrentPage = "Active"
End With
With .PivotFields("Location Code")
.Orientation = xlRowField
.LayoutSubtotalLocation = xlAtBottom
.LayoutForm = xlOutline
.LayoutCompactRow = True
.Subtotals(1) = False
End With
.PivotFields("Home Department Code").Orientation = xlRowField
End With
Sheets("Data").Select
'Overtime Check
Dim rng1 As Range
Dim sht1 As Worksheet
Dim pTable1 As PivotTable
Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
Set sht1 = ActiveWorkbook.Worksheets.Add
sht1.Name = "Overtime Pivot"
Sheets("Data").Select
Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
sht1.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
With pTable1
With .PivotFields("DOB")
.Orientation = xlPageField
End With
With .PivotFields("SSN")
.Orientation = xlRowField
.LayoutSubtotalLocation = xlAtBottom
.LayoutForm = xlOutline
.LayoutCompactRow = True
.Subtotals(1) = False
End With
.PivotFields("Home Department Code").Orientation = xlRowField
End With
Sheets("Data").Select
End Sub