Comment from Jeff Weir
Time: Monday, August 3, 2009, 4:02 am

Jon…this is an outstanding tutorial, even by your standards. Thank you, thank you, thank you.

This alone is worth the price of the several excel reporting and charting books I purchased that – rather annoyingly – don’t have this essential charting info in them.

Comment from Colin Banfield
Time: Monday, August 3, 2009, 10:32 am

An VBA object list described solely by visual worksheet examples. I’ve never seen a presentation quite like this. I think you’re on to something good here.

Comment from AlexJ
Time: Monday, August 3, 2009, 10:37 pm

Jon,
Make it two and a half smart guys.

Comment from Doug Glancy
Time: Tuesday, August 4, 2009, 12:09 am

Jon,

This is great. I’m sure I’ll refer back here again.

A few months ago I created a pivot table class, partly to teach myself this stuff. As part of it I created functions to return the row and column grand total ranges, because I didn’t see any vba properties for them. Here’s the on for the Row Grand Totals. It seems to work. What do you think?

Function RowGrandTotals() As Range

With pt
If .RowGrand = True Then
Set RowGrandTotals = .DataBodyRange.Offset(0, .DataBodyRange.Columns.Count – 1).Resize(.DataBodyRange.Rows.Count + (1 * .ColumnGrand = True), 1)
End If
End With

End Function

Comment from Jeff Weir
Time: Monday, September 21, 2009, 1:19 am

Hi Jon. I’ve a couple of questions for you re this post.

1. Where you have “Dim pt As Pivot Table” above, should this be “Dim pt As PivotTable” (i.e. PivotTable is one word with no space between Pivot and Table)?

In 2007 I need to take the space out to get your code to work.

2. In the case that you have the Pivottable’s Years field in the columns area, how would you select say the readins for Feb only for the years 2003 – 2005?

3. I have fields formatted as date/time information across the top of my pivottabel, and I amend your code accordingly, I get an error “Unable to get the PivotItems property of the PivotField class”. That is, your code can handle columns that are formatted as text, or even numbers that are formatted as text, but not numbers or dates formatted as dates.

For instance, The code works if I rename the pivot columns as text such as “a”, which obviously forces Excel to treat it as text which your vba can handle.

For instance, the SQL query that populates the pivot table shows a certain date as “1/11/2008 00:00″ (when viewing the raw SQL output in SQL Query Analyzer). My pivot table formats this as “1/11/2008″. But I get an error if I use a VBA statement like this:
pt.PivotFields(”Order Month/Year”).PivotItems(”1/11/2008 00:00″).DataRange.Select

…or this:
pt.PivotFields(”Order Month/Year”).PivotItems(”1/11/2008″).DataRange.Select

…or this:
pt.PivotFields(”Order Month/Year”).PivotItems(”39753″).DataRange.Select

However, if I overtype the field as something like “a” then this works:
pt.PivotFields(”Order Month/Year”).PivotItems(”a”).DataRange.Select

…and if I overtype the field as “1/11/2008″ (which sticks it in as text, not as a date) then this works:
pt.PivotFields(”Order Month/Year”).PivotItems(”a”).DataRange.Select

…and in both these cases, what I type in is automatically left-aligned as opposed to the original pivotfields, which are all right-aligned.

Interestingly, if I overtype 1/11/2008 with the number equivalent 39753, Excel doesn’t format it as text and the code fails. But if I then overtype 39753 with “a”, and then overtype “a” with 39753, excel treats it as text and the code works.

I take it from this that I need to amend the VBA in some way to tell it i’m looking for date/time information, not searching for text. Do you have any idea if this is the case, and if so, what kind of statement I need?

Thanks for any help.

Jeff

Comment from Jeff Weir
Time: Monday, September 21, 2009, 10:54 pm

Hi Jon. That’s great. Sorry, 2 more questions for you on this. (You are probably going to cringe when you see how little I understand of VBA…I’m still working my way though Walkenbach’s Power Programming).

1. Once I’ve used the loop you’ve posted above in answer to my last question, how do I then select the PivotItem concerned? For instance, I tried each of the following modifications to no avail (put them in the THEN part of your code) :
pt.PivotFields(”Order Month/Year”).PivotItems(Format(”1/1/2009″, “m/dd/yyyy”)).DataRange.Select
pt.PivotFields(”Order Month/Year”).PivotItems(Pi.Caption).DataRange.Select
pt.PivotFields(”Order Month/Year”).PivotItem.DataRange.Select

2. In your example, how would I modify pt.PivotFields(”Order Date”).PivotItems(”Feb”).DataRange.Select
to select say Feb’s figure for 2004 and 2005, but not 2003?
This gives me Feb’s figure for 2004:
Intersect(pt.PivotFields(”Order Date”).PivotItems(”Feb”).DataRange, pt.PivotFields(”Years”).PivotItems(”2004″).DataRange).Select

But I cant’ simply add in another year like this:
Intersect(pt.PivotFields(”Order Date”).PivotItems(”Feb”).DataRange, pt.PivotFields(”Years”).PivotItems(”2004″,”2005″).DataRange).Select

…as I get the error “Wrong number of arguements or invalid property assignment”

Sorry for the stream of questions.
Regards, Jeff

Comment from Jeff Weir
Time: Tuesday, September 22, 2009, 10:37 pm

Thanks Jon. Awesome.

Comment from LEM
Time: Tuesday, October 20, 2009, 8:13 am

Hey Jon,

Is there a way to apply a conditional format to a Pivot Table that would Bold Font and Highlight in Yellow entire rows that have met a certain condition in one of the columns?

Thank you!

Comment from LEM
Time: Tuesday, October 20, 2009, 9:21 am

Hmmmm… Interesting, because when I refresh my pivot it will keep the conditional format on the first three columns, but the rest of the columns will lose the format when it is refreshed. Then if I try to Apply it again it does not work, and I have to re-enter the formula. The Pivot Table is not ‘resizing’ due to a filter for the Top 100 Items… So I was hoping there was some way to automate this…

Thanks again for the help!

Comment from LEM
Time: Tuesday, October 20, 2009, 9:48 am

Sorry I keep having to post…

I am in Exel 2007, and my List Data is on a separate tab, I have the following in my PivotTable Field List:

1 Report Filter
2 Column Labels (One of which is ‘Values’ from the last Field List box)
3 Row Labels
9 Values

Even when I try pressing F9 (?) and refresh it will drop columns (from =$B$10:$V$109 to =$B$10:$D$109. (Only formatting my Row Labels, and losing formatting on all of the Values.

Because of this I was hoping I could include something within a Macro (that currently just does small things like column size adjustment) that would also update the conditional format Applied To Range.

Hope this makes sense :)

Comment from LEM
Time: Tuesday, October 20, 2009, 10:28 am

Just to add to the above:

And anytime I try to just ‘record’ my actions for conditional formatting nothing shows up…

Comment from LEM
Time: Tuesday, October 20, 2009, 12:36 pm

I would like to apologize in advance (and thank you for your patience!), because I am still very much a beginner in coding and trying to learn … But I am not sure exactly what to do… So far:

Sub PivotConditionalFormat()
‘
‘ PivotConditionalFormat Macro
‘
‘Bold and Highlight
pt.TableRange1.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:C =”=IF(C=”TEXT”,TRUE,FALSE)”
With Selection.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
End With

End Sub

But of course nothing works… I get errors thrown up all around the Formula and cannot get past this to even try it out – or see what other errors I have

Comment from Si
Time: Wednesday, October 21, 2009, 4:19 am

Hi Jon

Very useful post,

A quick question if I may, I am using your code to automate the formatting of a set of sheets with pivot tables, all of which are different . My aim is to open a the workbook, change the source data for the day click refresh all pivots and have all the pivots format themselves.

I have been using the below code to format all the pivot tables.

Sub Format_Pivots()

Dim PT As PivotTable
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook

For Each ws In Worksheets
For Each PT In ws.PivotTables
With PT.TableRange1
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
Next PT
If Not (wb.Name Like “5 Week PTL*”) Then
ws.Columns(”B:Z”).ColumnWidth = 8.14
ElseIf Not (wb.Name Like “Obstetrics Waiting List*”) Then
ws.Columns(”B:Z”).ColumnWidth = 8.14
Else
ws.Columns(”C:Z”).ColumnWidth = 8.14
End If
Next ws

End Sub

However when I use pt.ColumnRange.Select & pt.DataBodyRange.Select I cannot get it to work unless I use your code specifying pivot table 1.

What am I missing?

Comment from Rupert
Time: Tuesday, October 27, 2009, 2:06 am

Hi,
I was wondering if you know how to add comments to a pivot table via VBA.
I have data in a worksheet in the following format :

SalesStation,Date,Value,Comments
S01,01/10/2009,100
S01,02/10/2009,150,low sales on this day
S01,03/10/2009,120

and I would like to add any comments to the data field as a little red marker in the top right corner of the cell if there is a comment for that value.

Thanks

Comment from Rupert
Time: Tuesday, October 27, 2009, 7:50 pm

Thanks John,

This is what I have so far :

Private Sub addcomments(sheetname, pivottablename)
Dim c As Range, pvtitem As PivotItem, d As Range
With Sheets(sheetname).PivotTables(pivottablename)
If .RowRange.Count > 2 Then
For Each c In .PivotFields(”count of comments”).DataRange.Cells
If c.Value = 1 Then
Set d = Cells(c.Row, c.Column – 1)
d.AddComment “test”
Else
End If
Next
Else
End If
End With
End Sub

I can identify which cell to add the comment to but the problem is getting the text form the comments field actually into the d.addcomment “test” part above.

Do you have any ideas for it?

Thanks,
Rupert

Comment from Rupert
Time: Tuesday, October 27, 2009, 9:05 pm

Yes, I get a comment in the cell.

The problems are :

1) I have to add the comments field as a data field – “count of comments” – and do a count on it so it is visible.
I then have to add a comment to the same row, previous column in the value data field if a value exists in the “count of comments” data field, and then hide the “countof comments” data field.

2) i cant seem to extract the actual comments text from the comments field into the inserted comment in the value data field.

Let me know if you want me to send a sample to you.

Cheers,
Rupert

Comment from Rupert
Time: Friday, November 6, 2009, 12:22 am

Hi Jon,

Think I’ve got it. Heres what I did :

Sub insertcomments(sheetname, pipelinecode, processplantname, _
                   SalesStationCode, pivottablename)
  Dim datarange As Range, commentsrange As Range, custcode As Range
  Dim pprange As Range, ssrange As Range, catrange As Range, piperange As Range
  Dim d As Integer, mynames() As String
  Dim cust As String, datefield As String, pp As String
  Dim SSCode As String, cat As String, pipecode As String
  Dim pvtitem As PivotItem

  Worksheets("qryexcelexport").Select
  Set datarange = ActiveSheet.Range("A1").CurrentRegion
  ReDim mynames(datarange.Columns.Count)

  For c = 1 To datarange.Columns.Count
    mynames(c) = Cells(1, c).Value
  Next

  d = datarange.Rows.Count
  On Error Resume Next
  For e = 2 To d
    If Not IsEmpty(Cells(e, c - 1)) Then
      For f = 1 To c - 1
        Select Case mynames(f)
          Case "processPlantName"
            pp = Cells(e, f).Value
            Set pprange = Sheets(sheetname).PivotTables(pivottablename) _
                          .PivotFields("processPlantName").PivotItems(pp).datarange
          Case "SalesStationCode"
            SSCode = Cells(e, f).Value
            Set ssrange = Sheets(sheetname).PivotTables(pivottablename) _
                          .PivotFields("SalesStationCode").PivotItems(SSCode).datarange
          Case "CustomerCode"
            cust = Cells(e, f).Value
            Set custcode = Sheets(sheetname).PivotTables(pivottablename) _
                           .PivotFields("CustomerCode").PivotItems(cust).datarange
          Case "Category"
            cat = Cells(e, f).Value
            Set catrange = Sheets(sheetname).PivotTables(pivottablename) _
                           .PivotFields("Category").PivotItems(cat).datarange
          Case "PipelineCode"
            pipecode = Cells(e, f).Value
            Set piperange = Sheets(sheetname).PivotTables(pivottablename) _
                            .PivotFields("PipelineCode").PivotItems(pipecode).datarange
          Case "Date"
            datefield = Format(CDate(Cells(e, f).Value), "m/d/yyyy")
            Set daterange = Sheets(sheetname).PivotTables(pivottablename) _
                            .PivotFields("Date").PivotItems(datefield).datarange
          Case Else
        End Select
      Next

      With Sheets(sheetname).PivotTables(pivottablename)
        Set commentsrange = Intersect(pprange, ssrange, custcode, _
                                      catrange, piperange, daterange)
        commentsrange.AddComment Cells(e, c - 1).Value
      End With
    End If

    Set pprange = Nothing
    Set ssrange = Nothing
    Set custcode = Nothing
    Set catrange = Nothing
    Set piperange = Nothing
    Set daterange = Nothing
    Set commentsrange = Nothing
  Next

End Sub
 

Thanks for your time anyway, Rupert

Comment from Final Impact
Time: Thursday, November 12, 2009, 2:50 pm

Hi Jon and thanks for the above info it was helpful and insightful!
I am however at a loss as to how to do something simple and use cells from a sheets range like f2 to f32 to make items in a Pivot field ‘PivotItems Visible = True’. I’ve recorded macros and attempted to automate the process but I’m missing something. (Too many problems to list)

The PT comes from a table of test records with 30 columns and 15k rows. My PT is setup but I have to manually uncheck “Show All” and search for and check-off 200 serial numbers in column 1 of the PT every three days.
Can I point my vb code to a list or range cells and exclude everything in the column which is not in the column I specifiy (f2:f32 or A1 to A201) from another sheet?
PT headers are as follows: SERIAL_NUM, CAL_DATE, CAL_TIME, SLOT, ERROR_MESS, ect. and all I need is SERIAL_NUM visible equal to true for 200 of the 15k serial numbers available. Does this make sense?

Thanks for any input.
Final Impact