Pivot table VBA Macro for Overtime Report

[bchill]

Hello All,

I am sure this is pretty basic, but I have been searching the forum all day trying to find the answers. I could also use some help cleaning up my current macro, but I will leave that for the end.

I am basically trying to create a basic pivot table using the data I have in worksheet 1; columns A:I. The column headers are below.

<TABLE style="WIDTH: 485pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=646 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 id=_x0000_s1025 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: black" width=79 height=17 x:autofilterrange="$A$1:$I$1" x:autofilter="all">Emp #</TD><TD class=xl24 id=_x0000_s1026 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 146pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=194 x:autofilter="all">Employee Name</TD><TD class=xl24 id=_x0000_s1027 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 34pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=45 x:autofilter="all">Ttl Hrs</TD><TD class=xl24 id=_x0000_s1028 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 38pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=51 x:autofilter="all">Hrs Wk</TD><TD class=xl24 id=_x0000_s1029 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 40pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=53 x:autofilter="all">Hrs NW</TD><TD class=xl24 id=_x0000_s1030 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 35pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=47 x:autofilter="all">Hrs Pd</TD><TD class=xl24 id=_x0000_s1031 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 37pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=49 x:autofilter="all">OT HOURS</TD><TD class=xl24 id=_x0000_s1032 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=64 x:autofilter="all">DEPARTMENT</TD><TD class=xl24 id=_x0000_s1033 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: black" width=64 x:autofilter="all">GROUP</TD></TR></TBODY></TABLE>

This is my current attempt, but it never works the second time (the lower portion is my attempt at formatting the table):

    Sheets("PIVOT TABLES").Select
    Range("B4").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DATA!C1:C9").CreatePivotTable TableDestination:= _
        "'[Kronos TimeKeeper Reports Tool.xls]PIVOT TABLES'!R4C2", TableName:= _
        "OT TABLE", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("OT TABLE").PivotFields("GROUP")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("OT TABLE").PivotFields("DEPARTMENT")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("OT TABLE").PivotFields("DEPARTMENT")
        .PivotItems("GONE").Visible = False
        .PivotItems("REGIONAL").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("OT TABLE").AddDataField ActiveSheet.PivotTables( _
        "OT TABLE").PivotFields("OT HOURS"), "Count of OT HOURS", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("D7").Select
    ActiveSheet.PivotTables("OT TABLE").PivotFields("Count of OT HOURS").Function _
        = xlSum
    Columns("E:E").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Interior.ColorIndex = 2
    ActiveWindow.SmallScroll Down:=39
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Rows("56:56").Select
    ActiveWindow.SmallScroll Down:=-51
    Cells.Select
    Selection.Interior.ColorIndex = 2
    Range("B4:D4").Select
    Range("D4").Activate
    Selection.Interior.ColorIndex = 9
    Selection.Font.ColorIndex = 2
    Range("B4:D4").Select
    Range("D4").Activate
    Selection.Font.Bold = True
    Range("B5:D5").Select
    Range("D5").Activate
    Selection.Interior.ColorIndex = 48
    Range("B7:D7").Select
    Range("D7").Activate
    Selection.Interior.ColorIndex = 15
    ActiveWindow.SmallScroll Down:=3
    Range("B9:D9").Select
    Range("D9").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B25:D25").Select
    Range("D25").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B27:D27").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B29:D29").Select
    Range("D29").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B31:D31").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B33:D33").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B35:D35").Select
    Range("D35").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B38:D38").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B45:D45").Select
    Range("D45").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    ActiveWindow.SmallScroll Down:=6
    Range("B54:D54").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B55:D55").Select
    Range("D55").Activate
    Selection.Interior.ColorIndex = 48
    ActiveWindow.SmallScroll Down:=-57
    Range("A1").Select
End Sub

Thank you for your help.