Pivot table VBA Macro for Overtime Report

B

bchill

New Member
Joined
Aug 4, 2009
Messages
40
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):

Code: 
    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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
In your code you did not add any column fields, so there was no data to select when you started formatting with row E.

This code seems to replicate the portion of your code dealing with creating the Pivot Table. There is a bit of flexibility added:

Code: 
Sub WorkPivotTable()
    Dim lLastDataRow As Long
    Dim iX As Integer
 
    'Determine last row of data worksheet
    lLastDataRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("PIVOT TABLES").Select
    'Delete any existing pivot table
    Sheets("PIVOT TABLES").Cells.Clear
 
    Range("B4").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DATA!R1C1:R" & lLastDataRow & "C9").CreatePivotTable TableDestination:= _
        "'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")
        On Error Resume Next 'In case none of the following is present in "Department"
        .PivotItems("GONE").Visible = False
        .PivotItems("REGIONAL").Visible = False
        .PivotItems("(blank)").Visible = False
        On Error GoTo 0
    End With

Please create a data sheet with varied entries in all fields and post a dozen rows or so using Excel Jeanie (see link in sig). Run my code against that to see if it works. If it does turn on the macro recorder and format it as you desire and post that captured code. If it does not, explain what line it failed on and what the error message was.

Do not press the submit button multiple times, sometimes it takes a moment to have your post placed on the board.
 
Upvote 0
Thanks Phil.

Below is the data using Excel Jeanie. I ran the macro as suggested and it did everything except for dropping the data items (the numbers).

I will post the macro recorder formating if it will help in a couple minutes. I basically want the top bar to be Dark Red with bold white text, the header bar 40% gray with black text, and the other sub-total rows to be 25% gray with black text.
I would also prefer for the worksheet's background to be white.


DATA

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 194px"><COL style="WIDTH: 45px"><COL style="WIDTH: 51px"><COL style="WIDTH: 53px"><COL style="WIDTH: 47px"><COL style="WIDTH: 49px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Emp #</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Employee Name</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Ttl Hrs</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Hrs Wk</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Hrs NW</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">Hrs Pd</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">OT HOURS</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">DEPARTMENT</TD><TD style="FONT-WEIGHT: bold; COLOR: #ffffff; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center">GROUP</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">151903</TD><TD>a</TD><TD style="TEXT-ALIGN: right">40.56</TD><TD style="TEXT-ALIGN: right">30.56</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">40.56</TD><TD style="TEXT-ALIGN: right">0.56</TD><TD>HOUSTON DOMESTIC - PHASE 1</TD><TD>INBOUND TRACK/TRACE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">146433</TD><TD>b</TD><TD style="TEXT-ALIGN: right">50.07</TD><TD style="TEXT-ALIGN: right">50.07</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">50.07</TD><TD style="TEXT-ALIGN: right">10.07</TD><TD>HOUSTON DOMESTIC - PHASE 1</TD><TD>OUTBOUND DOCK</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">153218</TD><TD>c</TD><TD style="TEXT-ALIGN: right">45.31</TD><TD style="TEXT-ALIGN: right">37.31</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">45.31</TD><TD style="TEXT-ALIGN: right">5.31</TD><TD>HOUSTON INT WAREHOUSE</TD><TD>RECEIVING</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">136705</TD><TD>d</TD><TD style="TEXT-ALIGN: right">40.21</TD><TD style="TEXT-ALIGN: right">32.21</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">40.21</TD><TD style="TEXT-ALIGN: right">0.21</TD><TD>HOUSTON INT OIL LOGISTICS</TD><TD>OIL & GAS ACCOUNTS</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">165753</TD><TD>e</TD><TD style="TEXT-ALIGN: right">30.35</TD><TD style="TEXT-ALIGN: right">30.35</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">30.35</TD><TD style="TEXT-ALIGN: right">0</TD><TD>HOUSTON DOMESTIC - PHASE 1</TD><TD>INBOUND TRACK/TRACE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">161691</TD><TD>f</TD><TD style="TEXT-ALIGN: right">40.83</TD><TD style="TEXT-ALIGN: right">40.83</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">40.83</TD><TD style="TEXT-ALIGN: right">0.83</TD><TD>HOUSTON INT OIL LOGISTICS</TD><TD>OIL & GAS ACCOUNTS</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">150308</TD><TD>g</TD><TD style="TEXT-ALIGN: right">52.83</TD><TD style="TEXT-ALIGN: right">52.83</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">52.83</TD><TD style="TEXT-ALIGN: right">12.83</TD><TD>HOUSTON DOMESTIC - PHASE 1</TD><TD>OUTBOUND DOCK</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">161490</TD><TD>h</TD><TD style="TEXT-ALIGN: right">41.72</TD><TD style="TEXT-ALIGN: right">25.72</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">41.72</TD><TD style="TEXT-ALIGN: right">1.72</TD><TD>TRANSOCEAN</TD><TD>HAZMAT GROUP</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">138049</TD><TD>i</TD><TD style="TEXT-ALIGN: right">41.6</TD><TD style="TEXT-ALIGN: right">17.6</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">41.6</TD><TD style="TEXT-ALIGN: right">1.6</TD><TD>HOUSTON INT IMPORTS / BROKERAGE</TD><TD>IMPORTS / BROKERAGE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">165501</TD><TD>j</TD><TD style="TEXT-ALIGN: right">38.24</TD><TD style="TEXT-ALIGN: right">38.24</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">38.24</TD><TD style="TEXT-ALIGN: right">0</TD><TD>HOUSTON INT IMPORTS / BROKERAGE</TD><TD>IMPORTS / BROKERAGE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 8pt; FONT-FAMILY: Courier; TEXT-ALIGN: right">137356</TD><TD>k</TD><TD style="TEXT-ALIGN: right">40.12</TD><TD style="TEXT-ALIGN: right">32.12</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">40.12</TD><TD style="TEXT-ALIGN: right">0.12</TD><TD>HOUSTON DOMESTIC - PHASE 1</TD><TD>OUTBOUND DATA ENTRY</TD></TR></TBODY></TABLE>


Thank you for your help... And sorry about the multiple posts; I thought my computer froze.

Thanks.
 
Upvote 0
Here is the added formatting. The first few lines I added the values to the pivot table.


Code: 
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/17/2009 by iahopbxm
'
'
    ActiveSheet.PivotTables("OT TABLE").AddDataField ActiveSheet.PivotTables( _
        "OT TABLE").PivotFields("OT HOURS"), "Sum of OT HOURS", xlSum
    Range("B4:D4").Select
    Range("D4").Activate
    With Selection.Interior
        .ColorIndex = 9
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 2
    Selection.Font.Bold = True
    Range("B5:D5").Select
    Range("D5").Activate
    With Selection.Interior
        .ColorIndex = 48
        .Pattern = xlSolid
    End With
    Range("B9:D9").Select
    Range("D9").Activate
    Selection.Font.ColorIndex = 2
    Selection.Font.ColorIndex = 0
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Range("B11:D11").Select
    Range("D11").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B13:D13").Select
    Range("D13").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B15:D15").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B17:D17").Select
    Range("D17").Activate
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
    Range("B18:D18").Select
    Range("D18").Activate
    With Selection.Interior
        .ColorIndex = 48
        .Pattern = xlSolid
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub
 
Upvote 0
I believe this will do as you asked:
Code: 
Option Explicit
Sub WorkPivotTable()
    Dim lLastDataRow As Long
    Dim iX As Integer
    
    'Determine last row of data worksheet
    lLastDataRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("PIVOT TABLES").Select
    'Delete any existing pivot table
    Sheets("PIVOT TABLES").Cells.Clear
 
    Range("B4").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DATA!R1C1:R" & lLastDataRow & "C9").CreatePivotTable TableDestination:= _
        "'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")
        On Error Resume Next 'In case any specified are not present in "Department" column
        .PivotItems("GONE").Visible = False
        .PivotItems("REGIONAL").Visible = False
        .PivotItems("(blank)").Visible = False
        On Error GoTo 0
    End With
    ActiveSheet.PivotTables("OT TABLE").AddDataField ActiveSheet.PivotTables( _
        "OT TABLE").PivotFields("OT HOURS"), "Sum of OT HOURS", xlSum
        
    FormatPivotTable
    
End Sub

Sub FormatPivotTable()

    Dim rngPTRange As Range
    Dim rngCell As Range
    
    Cells.EntireColumn.AutoFit
    
    Set rngPTRange = ActiveSheet.PivotTables(1).TableRange1
    
    'Row 1
    rngPTRange.Rows(1).Select
    With Selection.Interior
        .ColorIndex = 9
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 2
    Selection.Font.Bold = True
    
    'Row 2
    rngPTRange.Rows(2).Select
    With Selection.Interior
        .ColorIndex = 48
        .Pattern = xlSolid
    End With
    
    'Rows with Total
    For Each rngCell In rngPTRange.Columns(1).Cells
        If InStr(rngPTRange(rngCell.Row, 1).Value, "Total") > 0 Then
            With rngPTRange.Rows(rngCell.Row).Cells
                .Select
                .Font.ColorIndex = 2
                .Font.ColorIndex = 0
                With Selection.Interior
                    .ColorIndex = 15
                    .Pattern = xlSolid
                End With
            End With
        End If
    Next
    Range("A1").Select
    
    'Last Row
    rngPTRange.Rows(rngPTRange.Rows.Count).Select
    With Selection.Interior
        .ColorIndex = 48
        .Pattern = xlSolid
    End With
    Selection.Font.Bold = True
    
    Set rngPTRange = Nothing
    
    Range("A1").Select
    
End Sub

I split it into 2 procedures. The first will call the second, and you can run the second to reformat the pivot table if you make any manual changes.
 
Upvote 0
Thank you very much. That worked perfectly.

I can't believe how simple you made that look.

Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
71
Micron
M
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
860
kerm007
K
Y
  • Question
Modify Macro to Select 1 .TXT File at a time, do changes on it (tab delimeter+column to text) to convert to .XLS file+save the file then select other
Replies
0
Views
371
yferrer
Y
S
  • Solved
Error due to missing date
Replies
3
Views
286
saracat2012
S
B
  • Solved
Recording macro pivot table error
Replies
2
Views
994
biglb79
B

Forum statistics

Threads
1,214,834
Messages
6,121,877
Members
449,056
Latest member
ruhulaminappu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top