Referencing Pivot Table Ranges in VBA

I’ve posted several examples of manipulating pivot tables with VBA, for example, Dynamic Chart using Pivot Table and VBA and Update Regular Chart when Pivot Table Updates. These examples included specific procedures, and the emphasis was on the results of the manipulation.

I thought it would be helpful to show some of the mechanics of programming with pivot tables. One important part of this is referencing the various ranges within a pivot table by their special VBA range names (which are actually properties of the Pivot Table object).

I’ll illustrate these special ranges using this simple pivot table, which comes from an example formerly available on the Microsoft web site (I can no longer locate it).

Pivot Table

In VBA, you can reference a pivot table using this code in a procedure:

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)

I’ve highlighted the various ranges using the indicated VBA commands.

TableRange1

pt.TableRange1.Select

Pivot Table - Table Range 1

TableRange2

pt.TableRange2.Select

Pivot Table - Table Range 2

RowRange

pt.RowRange.Select

Pivot Table - Row Range

ColumnRange

pt.ColumnRange.Select

Pivot Table - Column Range

DataLabelRange

pt.DataLabelRange.Select

Pivot Table - Data Label Range

DataBodyRange

pt.DataBodyRange.Select

Pivot Table - Data Body Range

PageRange

pt.PageRange.Select

Pivot Table - Page Range

Pivot Field LabelRange

pt.PivotFields("Years").LabelRange.Select

Pivot Table - Pivot Field Label Range

PivotField DataRange

pt.PivotFields("Years").DataRange.Select

Pivot Table - Pivot Field Data Range

Pivot Item LabelRange

pt.PivotFields("Years").PivotItems("2004").LabelRange.Select

Pivot Table - Pivot Item Label Range

Pivot Item DataRange

pt.PivotFields("Years").PivotItems("2004").DataRange.Select

Pivot Table - Pivot Item Data Range

pt.PivotFields("Order Date").PivotItems("Feb").DataRange.Select

Pivot Field DataRange

The next few examples show the same ranges as above, after pivoting the table’s Years field from the columns area to the rows area.

pt.PivotFields("Years").DataRange.Select

Pivot Table - Pivot Field Data Range

Pivot Item LabelRange

pt.PivotFields("Years").PivotItems("2004").LabelRange.Select

Pivot Table - Pivot Item Label Range

Pivot Item DataRange

pt.PivotFields("Years").PivotItems("2004").DataRange.Select

Pivot Table - Pivot Field Data Range

Complex Range

If a particular range isn’t specifically defined with a VBA property, you can use VBA range-extending properties EntireColumn and EntireRow and range operators Union and Intersect to define the range. For example:

Intersect(pt.PivotFields("Years").Pivotitems("2004").DataRange.EntireRow, pt.PivotFields("Order Date").DataRange).Select

Pivot Table - Complex Data Range

Update

A follow-up post shows how to use this new skill to Create and Update a Chart Using Only Part of a Pivot Table’s Data.

 

Peltier Tech Charts for Excel

Comments

  1. 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.

  2. Colin Banfield says:

    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.

  3. Jeff & Colin –

    When I went through this myself the first couple dozen times, I’d go to the Object Browser, read what it said, then when it wasn’t very clear, I’d run exactly the code I used here. Nothing like seeing something to understand it. Since few people are going to dig as deeply and obsessively as I did, it seemed like a good idea for a tutorial. If two smart guys like it, then I was on the right track.

  4. Jon,
    Make it two and a half smart guys.

  5. 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

  6. Doug –

    This works fine. Pretty much any range manipulation you can envision will be helpful. This article is to help people envision the pivot table structures in terms of properties of the Pivot Table object.

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

  8. Jeff –

    1. Yes, there is no space in PivotTable, good catch.

    2. pt.PivotFields(“Order Date”).PivotItems(“Feb”).DataRange.Select

    3. What I generally do is loop through the pivot items, and compare the pivot item caption to what I’m looking for:

    For Each pi In pt.PivotFields("Order Month/Year").PivotItems
      If pi.Caption = Format(TheDate, "m/dd/yyyy") Then
      ' or If DateValue(pi.Caption) = TheDate Then
        '' FOUND IT
      End If
    Next
     
  9. 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

  10. 1. Here’s what I had in mind:

    For Each pi In pt.PivotFields("Order Month/Year").PivotItems
      If pi.Caption = Format(TheDate, "m/dd/yyyy") Then
      ' or If DateValue(pi.Caption) = TheDate Then
        pi.Select
        Exit For
      End If
    Next
     

    2. To select the data for 2004 and 2005, you would use something like this:

    Union(pt.PivotFields("Years").PivotItems("2004").DataRange, _
        pt.PivotFields("Years").PivotItems("2005").DataRange).Select
     

    So the Feb data for these two years would be

    Intersect(pt.PivotFields("Order Date").PivotItems("Feb").DataRange, _
        Union(pt.PivotFields("Years").PivotItems("2004").DataRange, _
        pt.PivotFields("Years").PivotItems("2005").DataRange)).Select
     

    Keep in mind that you do not need to select something if you just need to extract the data.

  11. Thanks Jon. Awesome.

  12. 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!

  13. You can use the same conditional formula as you would with a worksheet range (non-pivot). If the pivot table resizes, you’ll have to reapply or remove the conditional formatting, but if the pivot table refreshes without resizing, the conditional formatting works fine.

  14. 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!

  15. I tested in Excel 2003 SP3, and the pivot table had three row fields and one data field. The data came from a list (“table” in 2007) and the data field values were volatile random numbers. Pressing F9 then refreshing the pivot table resulted in the formats updating to reflect the new values.

  16. 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 :)

  17. Just to add to the above:

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

  18. I haven’t used conditional formatting much in 2007, and I haven’t programmed conditional formatting much in any version.

    You could just do a generic formatting routine, following this pseudo-code:

    for each row in pivottable-pivotfield-datarange-rows
    ..if row.cells(1,1).value > threshold value then
    ….row-entire row of pivot table-format-color and bold
    ..else
    ….row-entire row of pivot table-format-plain
    ..end if
    next

  19. 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

  20. LEM –

    If you have to use VBA to reapply conditional formatting every time the pivot table is refreshed, you might as well take the easy way out, skip the conditional formatting, and apply the formatting directly in the VBA routine. I’ve written up an example in Pivot Table Conditional Formatting with VBA. Thanks for the blog topic.

  21. 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?

  22. Si –

    1. You’ve neglected to say which version of Excel you are using.

    2. I see no references to ColumnRange or DataBodyRange in the code.

    3. I removed the ColumnWidth stuff, and the rest of your code ran just fine in Excel 2003.

  23. 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

  24. Rupert –

    Like all expert VBA programmers, I considered your question, then fired up the macro recorder while adding a random comment to a random cell. Here is what it told me:

    Sub AddComment()
    '
    ' AddComment Macro
    ' Macro recorded 10/27/2009 by Jon Peltier
    '
    
    '
        Range("H6").AddComment
        Range("H6").Comment.Visible = False
        Range("H6").Comment.Text Text:="Jon Peltier:" & Chr(10) & "Low sales on this day"
    End Sub
     

    I trimmed the code to:

    Sub AddCustomComment()
    '
    ' AddComment Macro
    ' Macro recorded 10/27/2009 by Jon Peltier
    '
    
    '
        Range("H7").AddComment.Text Text:="Low sales on this day"
    End Sub
     

    It’s up to your code to identify the cell that should get the comment, and to remove comments when they are no longer applicable.

  25. 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

  26. Rupert – Are the comments visible in the pivot table?

  27. 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

  28. I’m thinking that putting the comments field into the rows area might help…

  29. 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

  30. 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

  31. dear:
    In your article you datarange reference data, as I do to reference a data item in the grand total column using de intersect property:

    in the example
    pt.DataBodyRange.Select

    as I do to select in the row

    Order Date “Jan” Grand Total “42447,15”

    this cell is not inside in the datarange ?.
    as do I reference the column grand total ?

    thanks you very much

  32. Jon this is quite a useful article. Definitely the closest thing I’ve found to what I’m looking for. I was hoping this might be an appropriate place to take me the last step.

    I see above that using VBA, there are easy ways to return whole ranges from a pivot table that will dynamically resize. However, is there a way to fetch a pivot table range from a formula that might reside on another worksheet? Basically, I have two separate pivot tables that contain some different data. I also have some formulas on another worksheet that reference ranges in both of these tables to do some calculations; for example, one of my formulas would include LINEST(). In my formulas, I would like to reference the pivot table ranges, which may change as I manipulate what’s displayed on the pivot table. Is this possible to do with formulas? All I can find so far is the GETPIVOTDATA() function, but the documentation indicates that this is only for the summary data, whereas I want to operate on the range itself.

    Regards,
    Craig

  33. Hi Craig –

    I think this could be done with UDFs. One approach is to create a UDF like this (untested):

    Public Function PivotRange(SheetName As String, PivotName As String, _
        FieldName As String, ItemName As String) As Range
      If Len(ItemName) > 0 Then
        Set PivotRange = ActiveWorkbook.Worksheets(SheetName).PivotTables(PivotName). _
            PivotFields(FieldName).PivotItems(ItemName).DataRange
      Else
        Set PivotRange = ActiveWorkbook.Worksheets(SheetName).PivotTables(PivotName). _
            DataRange
      End If
    End Function

    You could then call it like:

    =Slope(PivotRange("Sheet1","PivotTable01","Years","2004"),
    PivotRange("Sheet1","PivotTable01","OrderDate",""))
  34. Hmm, that is an interesting way of approaching this problem, and one that I had not considered before. I’ll give it a shot and let you know if it works. The concept seems like it should, though.

    Thanks a lot!

  35. Great post, extremely useful as an introduction.

    Question :
    When you have two calculations, we then have a pivot field names “data” (données in french). In order to keep international compatiblity, do you know a way to capture the range of this cell.

    Thanks a lot.

  36. Gaetan –

    Instead of searching for each pivot field in YourPivotTable.PivotFields, try one of these:

    For Each PvtFld In YourPivotTable.PageFields
    For Each PvtFld In YourPivotTable.RowFields
    For Each PvtFld In YourPivotTable.ColumnFields
    For Each PvtFld In YourPivotTable.DataFields

  37. Jon –

    I think I should only look at rowfield and columnfield for “data” as I don’t think “data” could be a datafields, neither could be a pagefield.

    That said, as I don’t know in advance what will be the orientation of the data, I preffer to scan every possibility (I guess this could be specific to my problem).

    Beside the speed, do you see any other inconvenient?

    Thank you for your answer.

    Gaetan

  38. Gaetan –

    In general there isn’t any real inconvenience to cycling through items in VBA. The computer does it behind the scenes, and if there isn’t a huge number of items, the user will not notice.

  39. That’s what I was thinking.

    Thanks for confirming.

    Gaetan

  40. Robert - Norwalk, Connecticut says:

    I made good use of your Pivot calls for vba above. I tried getting the “Grand Total” field and failed using pt.PivotFields(“Years”).PivotItems(“Grand total”).DataRange.Select. Is there something different about that column.
    Sorry, I may have missed something.

    Thanks

  41. Robert –

    The totals are not distinct fields, but are part of the DataBodyRange, if the totals are included in the table. The code below bolds the column of row totals, italicizes the row of column totals, and shades the grand totals light gray. The grand total will be bold and italic, since it’s in the row totals and column totals.

    Sub SelectGrandTotal()
      Dim pt As PivotTable
      Dim rGrandTotal As Range
      Dim rColumnTotal As Range
      Dim rRowTotal As Range
      
      Set pt = ActiveSheet.PivotTables(1)
      
      With pt
        If .ColumnGrand And .RowGrand Then
          With .DataBodyRange
            Set rGrandTotal = .Cells(.Rows.Count, .Columns.Count)
            rGrandTotal.Interior.ColorIndex = 15
          End With
        End If
        If .ColumnGrand Then
          With .DataBodyRange
            Set rColumnTotal = .Rows(.Rows.Count)
            rColumnTotal.Font.Italic = True
          End With
        End If
        If .RowGrand Then
          With .DataBodyRange
            Set rRowTotal = .Columns(.Columns.Count)
            rRowTotal.Font.Bold = True
          End With
        End If
      End With
    End Sub
  42. Hi Jon
    Excellent article, but could you give me the equivalent way to reference PivotTable properties in MS Access (say 2003 or 2007)? I am trying to change the ‘Fill color’ property of a ColumnAxis field using VBA.
    I can update the ‘Caption’ property, but I just can’t crack how to update the Backcolor or Fill color property using this code:

    The next line works…
    Me.PivotTable.ActiveView.ColumnAxis.FieldSets(0).Fields(0).Caption = “Event”

    The next line doesn’t work.
    Me.PivotTable.ActiveView.ColumnAxis.FieldSets(0).Fields(0).FillColor = vbYellow
    Nor this line…
    Me.PivotTable.ActiveView.PivotFields(0).FillColor = vbYellow

    I have tried FillColor, BackColor, SubtotalBackcolor, but I just can’t reference this object correctly. Do you know how to reference this object so I can update the ‘BackColour’ property with VBA?

    I appreciate your help.

    Michael Daly
    29 April 2010

  43. Michael – You’ll have to ask someone who uses Access.

  44. How can i change pagefield data of pivot using VBA

  45. Akshat –

    To put a field into the page area:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("alpha").Orientation = xlPageField

    To change which item appears in the page field:

    ActiveSheet.PivotTables("PivotTable1").PivotFields("alpha").CurrentPage = "a"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("alpha").CurrentPage = "b"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("alpha").CurrentPage = "(All)"
  46. I’m trying to create multiple pivot tables using a pivot cache from another worksheet. I just keep modifying the same pitot, what am I missing?

    Row = 1

    For i = 1 To pf.PivotItems.Count
    ItemName = pf.PivotItems(i)
    Set PT = PTCache.CreatePivotTable _
    (TableDestination:=RepSummary.Cells(Row, 1), _
    TableName:=ItemName)
    Row = Row + 30
    ‘Add fields to the new pivot
    With PT.PivotFields(“SALESREP_NAME”)
    .Orientation = xlRowField
    .Caption = “Sales Rep”
    End With

    With PT.PivotFields(“Total”)
    .Orientation = xlDataField
    .Caption = “Volume ($)”
    .Position = 1
    .NumberFormat = “#,##0”
    End With

    With PT.PivotFields(“Uph”)
    .Orientation = xlDataField
    .Caption = “Uph Volume ($)”
    .Function = xlSum
    .NumberFormat = “#,##0”
    End With

  47. Adam –

    Have you defined PTCache?

    When I do this manually, I copy the original pivot table, paste it where I want the new one, and modify the layout of the new pivot table. This might be easier in code as well.

  48. Jon:

    I have: Set PTCache = ActiveWorkbook.PivotCaches(1) ‘where 1 is the original cache i wish to use.

    I’ll try your idea of merely copying the table and then use the With command to alter the layout as you suggest.

    The complication also arises in that i’m using the field values pf.PivotItems from the original pivot table to create 32 different (new) pivot tables in a new sheet.

    I’ll let you know if the suggestion works out (i.e. if i’m smart enough to write the darn thing)…

    -Adam

    p.s. your visual tutorials are indeed helpful and will come in handy.

  49. This post is wonderfully helpful! I have a followup question: how do you reference data labels in a nested pivot table? For example I have a pivot table with two columns (number and percent) under each data label (original and revised).

    Original | Revised
    # | % # | %

    How would I reference, for example, the # data label in the Revised column? I can select the Revised label via the below”

    PivotFields(“Original or Revised”).PivotItems(“Revised”).LabelRange.Select

    but I’m not sure how to select the sublabels, for lack of a better term. Hope that makes sense…looking forward to any guidance you can offer. Thanks!
    Tiffany

  50. Adam – The macro recorder is your friend….

  51. Tiffany –

    Can you use Intersect? In pseudo-code:

    Range Wanted = Intersect(PivotFields("Original or Revised").PivotItems("Revised") _
        .LabelRange.EntireColumn, PivotFields("# or %").PivotItems("#").LabelRange)
  52. Jon;

    Quite true.

    Found my error…incidentally it was very simple, the Row +30 wasn’t enough to allot for the new pivot. Now the code works. When all else seems right…it is…look for the simple things and most often that’s it.

  53. Intersect works perfectly, thanks!!!

  54. Jon…using Excel 2007, if I use pt.PivotFields(“Years”).LabelRange.Select then it just selects the header “Years”only from the pivot table and not the items underneath.

    If I use pt.PivotFields(“Years”).PivotItems(“2004″).LabelRange.Select then I’ll get the 2004 items, same as you do.

    Any idea how I select all the rows under a particular pivotfield? (and preferably not the heading itself)

  55. Jeff –

    This might be a case where you have to build the range you want using Union() and Intersect().

  56. The problem was that the pt.RowRange.Select command (the way you are using it without the optional arguments) works differently for a pivot table constructed in excel 2007 than one constructed in excel 2003.

    * In a pivottable created in excel 2007, if you don’t supply the optional arguments then the default is to just selects the headers.
    *In a pivottable created in 2003, if you don’t supply the optional arguments this selects the headers and all the rows below. (note this happens for an excel 2003 pivottable that has been opened in excel 2007 too). Note that this is not what the help file says it does…the help file example supposedly selects just the row headers on the PivotTable report. Looks like an error that it’s working the way it is in 2003 to me.

    RowRange takes 2 arguments…a rowindex number and a columnindex number. Then it works like an index function i.e. it drops down x rows according to the rowindex argument and across y columns according to your columnindex arguement.

    So pt.RowRange(3,1) will select the cell two rows under the first column heading i.e. row 3, column 1. Note that pt.RowRange(3,10) will select a cell in the data area (because you only have 2 columns in this example, but you’ve asked excel to keep on heading west). So if you provide a columnindex number, this function doesn’t restrict you to returning cells from your row fields.

    However, if you omit the columnindex and just use pt.RowRange(3) then excel starts counting cells from left to right, dropping down whenever it reaches the last column in the row area (in this case, column 2). So you’ll be selecting column 1, row 2 of the pivot table.

    In any event, I think pt.RowRange.Select is not the right command to use if you want to select row columns as shown in your example above…certainly if your code might be used on an Excel 2007 pivottable. Instead, you should use pt.RowRange.Columns.Select

    If you want the first column, then you should use pt.RowRange.Columns(1).Select or replace the 1 with the name of the column you want in speech marks e.g. “Years”

  57. Gee, an inconsistency between 2007 and 2003. 1/2 of my recent project work has been building ribbon interfaces for projects being upgraded to 2007, and another 1/4 has been finding and programming around such inconsistencies. It keeps the revenues coming in, but it’s not very challenging or interesting.

  58. Hello Jon,
    I am new to pivot tables so I found your post extremely helpful but I could really use your help solving a problem.

    I am running MS excel 2003

    I am trying to create reports for specified time periods which will be defined in two cells (one for the start date and the other the end date) by the users. I was hoping that the code below would achieve this but I get an error and usually only display the last date. I have even tried adding a false condition thinking that was missing but I still get errors. I really appreciate your time.

    start_date = “E6”
    end_date = “G6”

    For Each pvtItem In ActiveSheet.PivotTables(“PivotTable3”).PivotFields(“Date”).VisibleItems
    If (pvtItem.Name > end_date) Then
    pvtItem.Visible = False
    End If
    Next pvtItem

  59. You have told VBA that the start and end dates are addresses E6 and G6, not the values in these cells. Use:

    start_date = ActiveSheet.Range(“E6”).Value
    end_date = ActiveSheet.Range(“G6”).Value

  60. Hi i got a project and i wish to know using the below code i will like to create using this method how to i get the cell range? if i can get this method i can then complete my project

    pt.TableRange1.Select – getting a Range example A1:D8 something like that.

  61. Nicholas –

    To get the range, use this syntax:

    strAddress = pt.TableRange1.Address

  62. So does this also apply to the grand total at the bottom as i have to know the particular cell address for different column associated with it

    Item | Example 1 | Example 2|
    Item1 | 2 | 3 |
    Item2 | 5 | 2 |
    Grand Total | 7 | 5 | <– Using this column to convert into the below

    Item | Example 1 | Example 2|
    Grand Total | 7 | 5 |

    Due to the changes in the raw data and there are many of such pivot tables with different conditions and the process required after that i cant just create another pivot table with the condition i want.

  63. Nicholas – I don’t understand. Could you explain in a bit more detail?

  64. Sorry, i will like to make individual Charts of the Pivot table and as well as a overall View.

    Image 1 – My Data – http://yfrog.com/mc74482374p
    Image 2 – My Pivot Tables – http://yfrog.com/8644108178p
    Image 3 – My Overall Table – http://yfrog.com/bh15920375p

    In my Overall Table i am required to update the Grand Total For each category( which is Example 1 to Example 4 as shown on my Data) and i have about 2 – 3 different Overall Tables so with different combination and after that i required to do a chart and also using the chart i need to do many other function which by using pivot table i am unable to access.

    I also need to produce a chart of different Category(from example 1 to example 4)

    its something like that. sorry to trouble you.

  65. Nicholas –

    If your data were shaped differently, it would be easy with another pivot table and without code. Keep columns A through E as is, put the example label in column F, and put the True/False in column G. You will have four rows per S/N with the other data repeated in the new rows. This way, you could put column F into the rows area of the pivot table, Column G into the columns area and into the data area, to get your summary.

    If the hidden label for the rows area is “Class”, you can also get your pivot tables from the new data arrangement: put Example (column F) in the page area, and column G in the data area and in the columns area, and Class in the rows area.

  66. this is a great post, i only wish i had found it sooner. i used the macro recorder to figure out how to change the date format of a pivot field, and since it was a row field i was changeing the macro recorder used the .Name proprty. Unfortunately when you put this in a VBA script it doesn’t change the date format of the pivot item it actually changes the name of the pivot field! i found some reference to .NumberFormat but according to MS documentation it only is for data fields. I tried it for a row field and it works anyway

    With .PivotFields(“Service_Date_From”)
    .Orientation = xlRowField
    .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    .NumberFormat = “m/d/yyyy”
    End With

    Here’s the M$ article

    “You can set the NumberFormat property only for a data field.”

    excel seems to be very picky, any other date format and the intersection command fails

    unless i am doing something really wrong here

  67. Hi Jon,

    Thanks – this site is a great reference. My quest is regarding Grand totals. Your reply to Robert with “Sub SelectGrandTotal()” helped “break the logjam” for me.

    I did arrive at a solution. However, it seems inelegant (and isn’t elegance in coding what we’re all after? 8^).

    Using your above PT example, I’ve been trying to grab specific Grand Total values.
    For instance, to get the Grand total for 2004 (a value of $156843.61) I arrived at the following structure:

    Sub GetYearTotal(sActiveYear, sDataValue )
    Dim iDataCol as Integer, rColumnTotal as Range
    
    For Each pvtitem In pt.PivotFields("Years").PivotItems
        If pvtitem.Name = sActiveYear Then
            iDataCol = pvtitem.DataRange.Column
            Exit For
        End If
    Next
    
    With pt
        If .ColumnGrand Then
            With .DataBodyRange
                Set rColumnTotal = .Rows(.Rows.Count)
            End With
        End If
    End With
    
    sDataValue = Cells(rColumnTotal.Cells.Row, iDataCol)
    
    End Sub

    Again, is there a better way to get ‘sDataValue’ with having to resort to row/column matrixing/intersecting?

    Thanks much!
    JonV

  68. Let me preface my reply by pointing out that elegance in code is overrated. Now, if it works, that’s elegant enough.

    I think I may have done something like this, pending a few tests to make sure it’s going to work:

    sDataValue = intersect(pvtitem.datarange.entirecolumn, rcolumntotal.entirerow).value

    Whether it’s more or less “elegant”, I don’t know. I didn’t bother to fill out the whole sub, so I can’t even gloat about mine being done in fewer lines.

  69. Thanks – thats much cleaner.

    I’ll run with this structure:

    Sub GetYearTotal(sActiveYear, sDataValue )
    Dim rColumnTotal as Range
    
    With pt
        If .ColumnGrand Then
            With .DataBodyRange
                Set rColumnTotal = .Rows(.Rows.Count)
            End With
        End If
    End With
    
    For Each pvtitem In pt.PivotFields("Years").PivotItems
        If pvtitem.Name = sActiveYear Then
            sDataValue = intersect(pvtitem.datarange.entirecolumn, rcolumntotal.entirerow).value
            Exit For
        End If
    Next
    
    End Sub

    Hey – one less variable – thats elegant!

    JonV

  70. Hi Jon, excellent write up. Used most of your you posted code for formatting Grand Totals above, but I’m having trouble finding how to get to the totals in column L. Hoping you might point me in the right direction.

    I can’t seem to get to anything in column L, Label or Data Ranges. Also, I’m not able to find a way to alternate the colors of the drives as shown above. I’ve tried everything, and all I can get is all drives as one color.

    Any help will be greatly appreciated. – Thanks (Excel 2003, and 2007)

    http://cid-82a2b12e3596784a.office.live.com/self.aspx/.Public/Drives.jpg

    With pt
    If .ColumnGrand And .RowGrand Then
    With .DataBodyRange ‘ Unknown
    Set rGrandTotal = .Cells(.Rows.Count, .Columns.Count)
    rGrandTotal.Interior.ColorIndex = 2
    rGrandTotal.Font.ColorIndex = 1
    End With
    End If
    If .ColumnGrand Then
    With .DataBodyRange ‘Row All Grand Total (Bottom)
    Set rColumnTotal = .Rows(.Rows.Count)
    rColumnTotal.Interior.ColorIndex = 1
    rColumnTotal.Font.ColorIndex = 2
    rColumnTotal.Font.Italic = True
    rColumnTotal.ColumnWidth = 7.71
    End With
    End If
    If .RowGrand Then
    With .DataBodyRange ‘Virtical SAN Grand Total
    Set rRowTotal = .Columns(.Columns.Count)
    rRowTotal.Interior.ColorIndex = 1
    rRowTotal.Font.ColorIndex = 2
    rRowTotal.Font.Bold = True
    rRowTotal.ColumnWidth = 7.71
    End With
    End If

    With .PivotFields(“Drive”).LabelRange.Select
    Selection.Interior.ColorIndex = 1
    Selection.Font.ColorIndex = 1
    End With

    With .PivotFields(“Drive”).DataRange.Select
    Selection.ColumnWidth = 5.86
    Selection.Interior.ColorIndex = 15
    Selection.Font.ColorIndex = 1
    End With

    With .PivotFields(“LOCAL”).LabelRange.Select
    ‘Selection.ColumnWidth = 5.86
    Selection.Interior.ColorIndex = 35
    Selection.Font.ColorIndex = 1
    End With

    With .PivotFields(“LOCAL”).DataRange.Select
    Selection.Interior.ColorIndex = 35
    Selection.Font.ColorIndex = 1
    End With

    With .PivotFields(“SAN”).LabelRange.Select
    Selection.ColumnWidth = 5.86
    Selection.Interior.ColorIndex = 19
    Selection.Font.ColorIndex = 1
    End With

    With .PivotFields(“SAN”).DataRange.Select
    Selection.Interior.ColorIndex = 19
    Selection.Font.ColorIndex = 1
    End With

  71. To get the total of one of the column fields, you need to determine how many column fields there are, and the index of the desired column field among all column fields. There are as many column field totals as column fields, and the index of the total is the same as of the column field. Lots of intersect-type range definition.

    You’ve defined colors for the LOCAL and SAN parts of each drive. To define individual colors for each drive’s LOCAL and SAN data, you’ll need to use Intersect(Drive, Data) to identify the range.

  72. Hi Jon, please i need you help

    i have this code:

    Range(“IO17”).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    “Despiece!R4C1:R3000C45”
    ‘ActiveSheet.PivotTables(“Tabla dinámica1”).RefreshTable
    Dim ThisPage As String
    PageField1 = ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(1)
    OrigPage = ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(PageField1).CurrentPage
    NumPages = ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(PageField1).PivotItems.Count

    For j = 1 To NumPages

    ThisPage = ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(PageField1).PivotItems(j)
    If ThisPage = FrmDespachos.SpreadDes.Cells(i + 1, 1).Text Then
    ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(PageField1).CurrentPage = “(All)”
    ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(PageField1).CurrentPage = ThisPage
    ERROR 13 error 13 Type Mismatch
    ERROR 13 error 13 Type Mismatch

    ‘ActiveWindow.SelectedSheets.PrintOut
    End If
    Next j

    ‘ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(“DESP.”). _
    CurrentPage = FrmDespachos.SpreadDes.Cells(i + 1, 1).Text

    I found the error but still can not find the solution, the problem is that when I load my PivotTable refresh data then does not take a fact as the first element to be filtered, in office 2000 after loading the data (there this in my code) has already leaked the PSED data. = “0003” but in 2007 this office DESP .= “All”

    Is there any way that my pivot table after load data is selected filter element and is not in “All”

    Regards

    Orison
    From Lima Peru :)

  73. Don’t leave off the properties of the various objects. The default property may not be what you expect.

    ActiveSheet.PivotTables(“Tabla dinámica1″).PivotFields(1).Caption
    ActiveSheet.PivotTables(“Tabla dinámica1″).PivotFields(PageField1).CurrentPage.Name
    ActiveSheet.PivotTables(“Tabla dinámica1″).PivotFields(PageField1).PivotItems(j).Caption

    Also, CurrentPage is not defined if the PivotField is not a page field?

  74. Thanks, i have a solution :)

    ***********************************

    With ActiveSheet.PivotTables(“Tabla dinámica1”).PivotFields(“DESP.”)
    .PivotItems(strDespacho).Visible = True
    End With
    ************************************************

    Now i hace new problem with this code :
    ***************************************************

    Sheets(“FORMAS”).Select
    Range(“A1”).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ‘LENTO
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveWindow.Zoom = 75

    the problem is that when you copy data to another excel sheet everything is perfect copy raw data, then copy the format for the 19 entries, but I see the excel as having selected or openness, but plain cells but is selected as 1046215 incredible, so in this line of paste forms takes too long and slows the process. In Office 2000 is fast I have no problems but it is slow in Office 2007. You know that is?

    Thank you.

    Orison
    ***********************************************************

  75. Orison – What did you copy?

  76. Hi Jon

    My code to copy is:

    *********************************************************************
    Range(“A2”).Select
    Selection.Sort Key1:=Range(“A2”), Order1:=xlAscending, Key2:=Range(“F2”) _
    , Order2:=xlAscending, Key3:=Range(“G2”), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Selection.AutoFilter
    Range(“A2”).Select
    Selection.AutoFilter Field:=1, Criteria1:=DESPACHO
    Columns(“A:AB”).Select
    Selection.Copy
    Windows(Name & “.xlsx”).Activate

    Only copy 20 Rows.

    Thanks.

    Orison

  77. Orison –

    You say you only copied 20 rows, but

    Columns(“A:AB”).Select
    Selection.Copy

    selects them all. Try:

    Intersect(Range(“A1”).CurrentRegion, Range(“A:AB”)).Copy

  78. Ohhh thanks, (Y) is perfect :)

    Now I have new problem.

    The problem I have is that my whole process of copying data files to generate an Excel spreadsheet to another sheet Excel etc filtering data erasing data.
    When I run everything on Office 2000 there was no problem at all, everything was fine. Now that I’m almost finished the migration to the version of Office 2007 I have many problems, I’ve been spending almost all problems, I have almost resolved.
    Now I face the latter problem and story.

    It turns out that in my VBA code is a code like this:

    Workbooks.Open FileName: = “C: \ FORMS \ LIBRARY \ EXCEL6 \” & FORM &. “Xlsx”
    With ThisWorkbook
    . UpdateLinks = xlUpdateLinksNever
    End With
    Windows (FORM &. “Xlsx”). Activate
    Sheets (FORM). Select
    ‘DRAWINGS AND COPY ALL THE TIME OF DELIVERY: ORISON
    Sheets (FORM). Copy Before: = Workbooks (Name & “. Xlsx). Sheets (1)

    Copies data from one excel to another excel, format and copy it all and as far as copying, also copy an image.

    When I do step by step with the VBA debugger “F8” goes to copy all the information including the image, but when I run the whole process fails to copy the image, that’s the only problem, then once my trial ended I see my result and where it should be the images you see this text:

    “Unable to display the image. Your computer may not have enough memory to open the picture or it is damaged. Restart the computer and then open the file again. If you still see the red X, you may have to delete the image and insert it again.”

    Ever happened something? You know it could be due? the PC where I am working this process is:

    Intel Pentium Core 2 Quad Q9400 2.66Ghz with 3.21 GB ram.

    Thanks

    Orison from Lima Peru :)

  79. Orison –

    Yes, Excel 2007 seems to have problems with shapes and images in workbooks from earlier versions.

    You may improve things by:

    1. Inserting this line between lines of your procedure:

    DoEvents

    2. Reactivating screen updating (which will slow everything way down):

    Application.ScreenUpdating = True

    3. Use .Move instead of .Copy to insert the sheet into the other workbook. Then make sure you close the source workbook without saving, so the copy on disk still contains the moved sheet.

    Other remedies involve chicken livers and incense at midnight.

  80. Dear Jon, everything went very well as I mentioned only change rather than moving to copy and leave the rest as you advise me:) I’m eternally grateful:) this is only for masters like you:)

    I have a couple of queries.

    1.-What is the best method to call Oracle stored procedure returns a cursor to me, brings to use this method:

    Global objConnectionX, objCommand, objRecordset, objParameter

    Set objConnectionX = CreateObject(“ADODB.Connection”)
    With objConnectionX
    .ConnectionString = “Data Source=testa10;” & _
    “User ID=USUARIO;” & _
    “Password=CLAVE;”
    .Open
    End With

    Set objRecorset = CreateObject(“ADODB.Recordset”)
    With objRecorset
    objRecorset.CursorLocation = adUseClient
    objRecorset.CursorType = adOpenForwardOnly
    End With

    Set objCommand = CreateObject(“ADODB.Command”)
    With objCommand
    Set .ActiveConnection = objConnectionX
    .CommandType = adCmdText
    .CommandText = “{call ACDIM.IDELEPK_PRUEBAS.IDELESP_OT(?,{‘12345678’,ResultSet 0,pcurOT })}”

    Set objParameter = CreateObject(“ADODB.Parameter”)
    Set objParameter = .CreateParameter(“OrdenTrabajo”, adBSTR, _
    adParamInput, , ot)
    .Parameters.Append objParameter

    ‘Ejecuta el paquete almacenado. El cursor devuelto lo almacena en la variable objRecordSet
    objRecordset.Open objCommand

    Do While Not objRecordset.EOF
    ‘…..
    Loop

    ****************************************************
    My procedure is :

    PROCEDURE idelesp_OT(pvarOT IN VARCHAR2, pcurOT OUT obj_cursor
    )
    is
    begin
    open pcurOT FOR
    Select * from IDELET_CABECERA_DESPACHO
    where ORTRV_NRO_OT=pvarOT;

    end idelesp_OT;
    ****************************************************
    But I have good results, I have problems. I DONT KNOW WHAT HAPPEND when

    objRecordset.Open objCommand

    :(

    2.-It is possible to use VBA in Excel 2007 to bring data webservices? what happens is that I have to create a login and user in order to access the Oracle database and can not be used directly for security Single Sign On and recommended me to do it for webservices, you can really do that with VBA?

    Thanks for all the support and assistance.

    Sincerely,

    Orison

  81. 1. I haven’t used Oracle in this way, nor have I done much with this kind of query. My first step would be Google.

    2. You can use VBA for this, and again I’ll refer you to Google.

  82. I am trying to figure out how to read the contents of a PageField when EnableMultiplePageItems is set. The CurrentPage property of the field is set to a PivotItem whose Value is (All). All PivotItems are visible. The VisibleItems is set to an item whose value is (All) and each item is listed in teh HiddenItems list.

  83. Dear Jon

    Hi, I have a goal to accomplish.

    This is the macro security.

    I currently use plug-ins (*. XLAM) but the truth is that they tell me they do not believe they are very safe.

    The master sheet (*. XLSM) is the one that has data entered or better *. XLSM data feeds and webservices *. XLAM is responsible for making all the math formulas and all the data.

    What I would like to know, under this scheme, which could be safer? XLAM stop using and instead use either *. DLL developed with VB 6.0 or. NET? What do you recommend?

    Regards.

    Orison – Lima – Perú

  84. “I currently use plug-ins (*. XLAM) but the truth is that they tell me they do not believe they are very safe.”

    Who says this? How do they define “safe”? Do they really think an XLAM file is less “safe” than an XLSM file?

    Using an Excel add-in is a much simpler process than using COM or .Net, it’s “safe”, and usually it’s sufficient for the job.

  85. Hi Jon,

    I want to tell which is the reason why I seek a more optimal form of security.

    My process is as follows:
    Remote users only:
    1.-filled Excel pre-filtered data selected from the intranet
    2.-The Excel and all the add-in down to the remote user’s PC
    3.-The user runs the master Excel (*. XLSM) and you enter data, in turn, the master file makes use of component (*. XLSA) to make calculations and generate data, files etc.

    Hey here an important issue, we talk about security.

    The fact that the component (*. XLSA) and is in a remote desktop, a program could easily crack the key and see all the code that XLSA.

    I’m thinking of the following option:
    1.-Do not use XLSA and instead use DLL

    Is it possible?, I have experience with DLL and COM issue but it is doing all a long, long or at least maybe use a special program to ensure XLAM *.

    Regards.

    Orison

  86. When you said “safe”, at first I thought you wanted to keep the computer safe from malware.

    You could certainly build a DLL for the code, instead of using an XLAM file. This adds some security, some complexity, and probably some decline in performance. Is this a commercial product? If not, what’s the big deal?

  87. Hi Jon

    Is an application will use people outside of the company with confidential information. What we want is to protect all possible files (XLAM and XLSM) and can not be easily hacked.

    Orison

  88. Orison –

    Can you design the project so that the code is kept separate from any confidential information? Let the information be provided by the user, and processed by your code within the user’s workbook. Then keeping the data safe is completely within their control.

  89. Hi there,

    I have a question hopefully you could help me with please.

    I have a pivot table with a date and builder selection at the top
    On the left I have the category of work he did on that date and in the middle I have a sum of the amount he was paid gotr each category of work.
    Once I select a date (filter) there are about 50 builders (filter) which I can select.
    Each time I select a new builder the pivot table populates with his info.

    What I would like is a macro which automatically selects the first builder in the pivot filter list, then prints out the pivot table, then selects the second builder and prints out his pivot table info, 3rd…4th…5th builder etc…
    Is this possible?
    Thanks alot,
    Andre

  90. Hi Andre –

    Not too complicated:

    Sub PrintEachPivotItemInPivotField()
      Dim pt As PivotTable
      Dim pf As PivotField
      Dim pi As PivotItem
      Dim iItem As Long
      Dim jItem As Long
    
      Set pt = ActiveSheet.PivotTables(1)
      Set pf = pt.PivotFields("Builder")
    
      For iItem = 1 To pf.PivotItems.Count
    
        Application.ScreenUpdating = False
    
        pf.PivotItems(iItem).Visible = True
        For jItem = 1 To pf.PivotItems.Count
          If jItem  iItem Then
            pf.PivotItems(jItem).Visible = False
          End If
        Next
    
        Application.ScreenUpdating = True
    
        ActiveSheet.PrintOut
        
      Next
    End Sub
    		
  91. Hi Jon. I’m unsuccessfuly trying to build a pivot table via VBA from data in one workbook and have the resulting pivot table placed directly into another workbook (as opposed to another worksheet). Do you know if this is possible?

    THis code to create the pt within the same workbook works (with an added [pivot_source.xlsm] qualifier on SourceData and TableDestination) works:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “[pivot_source.xlsm]Sheet1!R1C1:R5C2″, Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination”[pivot_source.xlsm]Sheet1!R3C9″, TableName:=”PivotTable17”, DefaultVersion _
    :=xlPivotTableVersion14

    But this code (with SourceData qualified with [pivot_source.xlsm] and TableDestination qualified with [Pivot_Destination.xlsm] does not. I get the error ‘Run time error 5: Invalid procedure call or arguement’.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “[pivot_source.xlsm]Sheet1!R1C1:R5C2″, Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:=”[Pivot_Destination.xlsm]Sheet1!R3C9″, TableName:=”PivotTable17”, DefaultVersion _
    :=xlPivotTableVersion14

    Does this mean this can’t be done in one step? Reason why I was trying to put it directly into a new workbook is that the source workbook is write protected, which I would otherwise have to remove.

  92. I’m guessing this can’t be done. Help menu says” The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression.”

    I should have consulted the help menu first.

    Perhaps I can do this programatically by first opening a new workbook with vba, then setting up a connection string that goes from the destination to the source, then building the pivot table using that connection string (i.e. via the ‘use an external data source’ option on the pivottable dialogue box.

  93. This code works fine:

        Workbooks("Dest.xls").PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            "[Source.xls]Source!R1C1:R9C3").CreatePivotTable TableDestination:= _
            "[Dest.xls]Destination!R3C1", TableName:="PivotTable3", DefaultVersion:= _
            xlPivotTableVersion10

    as long as the destination workbook “Dest.xls” is open and it has a sheet named “Destination”. Doesn’t matter whether the source or destination (or any other) workbook is active.

    By specifying the parent workbook of the pivot cache, you avoid any problem of the pivot table and pivot cache being in different workbooks.

  94. Thanks Jon…I simply forgot to qualify Pivotcaches.add with the right workbook. There’s 2 hours I feel stupid about!

    I was just thinking about how many people you must have saved hours if not days, and I thought “It’s about time I clicked that ‘Buy me coffee’ link and see where it goes”. But now I can’t find the damn thing.

    I see from another comment elsewhere you’ve removed it because it was pretty much a distraction. But then, so is answering my queries on behalf of my employer!

    Great stuff, I’ll send you a get out of jail free card or something.

  95. Hi Jon,
    Im Francisco from peru

    i need you help

    im working with DLL, and i have passed this code from macro

    “”EXAMPLE1″”
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, (c0 + 3) * c + (4 + Luz(n)) * c * (i – 1), (f0 + 1) * f, Luz(n) * c, 2 * f).Select
    selection.Name = “Pic_RS_Lu_” & i
    Selection.ShapeRange.Fill.Visible = msoFalse
    Selection.ShapeRange.Line.Visible = msoFalse
    Selection.ShapeRange.Line.DashStyle = msoLineRoundDot
    Selection.ShapeRange.Line.Weight = 0.25
    Selection.OnAction = “AddDiaRi”

    “”EXAMPLE2″”
    n = Sheets(“Data”).Cells(3, 2)

    to dll (I am using visual basic 6 to create dlls)

    “”EXAMPLE1″”
    Excel2007.ActiveSheet.Shapes.AddShape(msoShapeRectangle, (c0 + 3) * c + (4 + Luz(n)) * c * (i – 1), (f0 + 1) * f, Luz(n) * c, 2 * f).Select
    Excel2007.Selection.Name = “Pic_RS_Lu_” & i
    Excel2007.Selection.ShapeRange.Fill.Visible = msoFalse
    Excel2007.Selection.ShapeRange.Line.Visible = msoFalse
    Excel2007.Selection.ShapeRange.Line.DashStyle = msoLineRoundDot
    Excel2007.Selection.ShapeRange.Line.Weight = 0.25
    Excel2007.Selection.OnAction = “AddDiaRi”
    “”EXAMPLE2″”
    n = Excel2007.Worksheets.Item(“data”).Cells(3, 2).Value

    my problem is when i try to pass this code to DLL

    Excel2007.Cells(11, mm + 3).FormulaR1C1 = “=ESTRIBO(RC[-4],RC[-2])”

    this is my problem ==> “=ESTRIBO(RC[-4],RC[-2])”

    ESTRIBO is a function

    regards

    thanks

  96. Is “ESTRIBO” a built-in function in Spanish, or is it the name of a custom function? You may need to use FormulaLocalR1C1 rather than FormulaR1C1, if “ESTRIBO” is the Spanish name of a built-in function.

    I don’t know what to say about the DLL.

  97. Jon

    I’ve read your tutorial with great interest and the points raised have been a great help. I do have a semi-related query to which I cannot find a solution. I am creating a pivot table which summarises a large number of daily readings from some CSV files . For presentation purposes, I need to hide the first and last days as the data for these two has to be excluded. I have no problem in hiding the last day (“EndDate”) but cannot hide the first day (“StartDate”). My code is shown below. “EndDate” and “StartDate” are already dimensioned earlier in the code as Dates. I am a rank amateur at VBA so the answer is probably something very simple but would appreciate your help.

    Many thanks, Gus

    ‘Attempt to hide the first and last readings as these are part days only and need to be excluded

    Dim pt1 As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Set pf = Sheets(“Summary”).PivotTables(“PivotTable1”).PivotFields(“Date”)
    pf.EnableMultiplePageItems = True

    For Each pi In pf.PivotItems
    pi.Visible = True
    Next pi

    For Each pi In pf.PivotItems
    If pi.Value = StartDate Then pi.Visible = False

    If pi.Value = EndDate Then pi.Visible = False

    Next pi

    Application.ScreenUpdating = True

  98. Gus –

    Make sure the earliest pivot item exactly matches your StartDate, or maybe use something like

    If pi.Value < StartDate + 1 Then pi.Visible = False

  99. John

    Thanks for your suggestion. I can confirm that the earliest pivot item matches the StartDate. However, the use of the “<" symbol hides 9 days instead of just 1, irrespective of the number that you insert after the symbol. I don't suppose it could be a function of the date being in a European rather than a US format, could it?

    Regards

    Gus

  100. Gus –

    Step through the For Each pi In pf.PivotItems code, and see what the StartDate and pi values are. Also, try pi.Caption instead of pi.Value, in case that makes a difference.

  101. I keep confusing myself with this, as I’m so new to it, so hope you can help :)

    In my pivot table “Data” I have a “Month” field that contains a difference from column of “MoM”.
    What I need to do is, if the cells in MoM are >0 then make it green, if <0 then make it red.
    Thought it would be simple enough after reading through this and the other related post, but I keep messing it up.
    Thanks for any help!

  102. Hi Jon

    After time I’m re back, and I have a query large enough Then I created a DLL in Visual Basic 6 where I have many procedures such as PivotTables, Excel file creation,
    Reading external data etc. 999.99% Everything is Ok but there is a problem and can not find the way resolve.

    It turns out that a cell in Excel I have a formula for something like this
    example:

    = IF (FC (-14) = 0, 0, FC (-4) * PESO (FC (-2), EXTRAE (FC (-14), 2, LARGO (FC (-14)) -1)))

    EXTRAE and LARGO function is inside a DLL in the new version.

    The reason for not supplements work is for safety, I prefer all my work with DLL functions with accessories.

    Well here the problem. It turns out that every time you change a value in a cell and another cell reference to change, should be changed automatically when you make a LostFocus, but there is no such thing similar, then the closest thing I found in VBA is to use:

    Worksheet_SelectionChange Private Sub (ByVal Target As Range)
    Call Calculo_de_Datos
    End Sub

    This really is but I can not give you a range of cells, it does for all cells, I wonder if there is any way to optimize this process or failing to create a:

    Worksheet_ChangeCellRow Private Sub (ByVal Target as Range))
    Where parameters can give if I want to make a change for me a whole range or cell to cell or else something like that to a LostFocus When the cell loses focus make the change or call a function and make calculations.

    Regards

    MERRY CHRISTMAS

  103. Wendy –

    This might be a simple conditional formatting task. Select the range, go to Conditional Formatting (Format menu in 2003, I forget where in 2007), and apply a simple rule of >0 is green and <0 is red.

  104. Yeah, you’d think so :P but (and I don’t know if this is a 2007 specific thing) when you refresh your table, all conditional formatting is lost, hence the vba route.
    I actually found a way to get it to work for filling the cells but the same doesn’t work for font colour. Strange!
    All the code is at work, so can’t post the example here.
    I’ve also been asked to try and get the conditional formatting icons to work… which it kinda does with an example I found, until the table is refresh and all the icons show the wrong thing.
    Ah well!
    It’ll be something to play with I guess…

  105. Orison –

    You can use this syntax:

    [MyRange].Calculate

    to calculate a specific range and it’s dependent cells.

  106. Actually, managed to grab the two examples. The first is for filling the cells, which works. The second is for the icons, which I still need to fix.

    ‘Sub FormatPT2()

    ‘Dim oFc As FormatCondition

    ‘ActiveSheet.PivotTables(“Data”).PivotSelect “MOM”, xlDataOnly, True

    ‘Set oFc = Selection.FormatConditions.Add(xlCellValue, xlEqual, “0”)

    ‘oFc.Interior.ColorIndex = 15

    ‘Set oFc = Selection.FormatConditions.Add(xlCellValue, xlLess, “0”)

    ‘oFc.Interior.ColorIndex = 43

    ‘Set oFc = Selection.FormatConditions.Add(xlCellValue, xlGreater, “0”)

    ‘oFc.Interior.ColorIndex = 3

    ‘End Sub

    Sub FormatPT2()
    ActiveSheet.PivotTables(“Data”).PivotSelect “MOM”, xlDataOnly, True
    Selection.FormatConditions.Delete
    ‘ First icon set
    Selection.FormatConditions.AddIconSetCondition
    Selection.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3Arrows)
    Selection.FormatConditions(1).ReverseOrder = True

    End Sub

  107. Wendy –

    Does the pivot table stay the same size? If so, move it out of the way, and set up another range that is linked to the pivot table (copy the pivot table, and use Paste Link). Then apply your conditional formatting to this pasted range.

  108. It’s an ever changing table I’m afraid. Contains information for multiple countries, so the idea is you use the filter to view one country at a time.

  109. Jon

    I believe I’ve isolated the problem that I’ve been having with my “pi.value” settings. It boils down to date and string formats. My StartDate and EndDate are dimensioned as Dates which are formatted in UK style dd/mm/yyyy.

    Using your suggested solution “If pi.Value < StartDate + 1 Then pi.Visible = False",
    when I run the macro, the pf.numberformat is shown as a dd/mm/yyyy string but the pi.caption shows a string in US format mm/dd/yyyy so consequently does not recognise the StartDate and EndDates. As an example, my StartDate is 07/12/2010 in UK format but pi.value is interpreting this as 12/07/2010. Consequently, I am getting a result where the macro is hiding the dates from 07 to 11/12/2010.

    This would also explain why my original didn't work either. Unfortunately, I can't think of an easy solution to this.

    Regards

    Gus

  110. VBA should know the correct date, provided your regional settings are consistent. If your date is 7-Dec-2010, and you write it as 07-12-2010, VBA may call it 12-07-2010, but it will know it as 7-Dec-2010. You could see if using the dd-mmm-yyyy number format helps, or you could test the formula of the cell (which for a cell without a formula is its unformatted value, 40519).

    But you can’t test pi.formula. You have to reference the cell, and test its formula. In other words, you need to change this:

    activesheet.pivottables(1).pivotfields(“Date”).pivotitems(3).caption

    to this:

    activesheet.pivottables(1).pivotfields(“Date”).datarange.cells(3).formula

  111. Hi John

    Problem solved. It is a date format issue. The pivot table defaulted to the system date settings “m/d/yyyy” so by changing these via the pivot table macro to the the format “dd/mm/yyyy;@”, I can now get the correct result. It seems a bit obtuse but it works.

    Thanks for your help.

    Regards

    Gus

  112. Hi everybody

    Hi have a really really problem, buy i can’t copy imges from excel file, i don’t understand.

    This the code:

    Application.ScreenUpdating = false
    Workbooks.Open FileName:=”C:\FORMAS\LIBRERIA\EXCEL6\FORM100.xlsx”
    With ThisWorkbook
    .UpdateLinks = xlUpdateLinksNever
    End With

    Windows(FORM & “.xlsx”).Activate
    Sheets(FORM).Select
    Sheets(FORM).Copy Before:=Workbooks(Name & “.xlsx”).Sheets(1)
    ‘*****************************************************************
    It turns out that when using “Application.ScreenUpdating = true” is all
    macro process and I have no problem with anything since the objective
    of this code is to copy a file from Excel Sheets with Excel file
    master.
    But when I use “Application.ScreenUpdating = false” are not the
    macro processes but does not copy the images to Excel master, not
    really understand it is due.
    Has anyone had similar problems and has been resolved?

    Regards.

    Orison

  113. Orison –

    I’ve seen some funny stuff in Excel 2007, where certain operations involving charts (and presumably shapes as well) don’t work right if ScreenUpdating is False. This is very frustrating, because (a) procedures slow way down when ScreenUpdating is True, and (b) it’s impossible to know a priori which operations will misbehave.

    Unrelated, but you should be aware that ThisWorkbook is the one containing the code, not the one you just opened.

    Also, you should avoid using a keyword like “Name” as a variable name.

    And the last three lines of code you’ve posted could be replaced by one line:

    Workbooks(FORM & “.xlsx”).Sheets(FORM).Copy Before:=Workbooks(Name & “.xlsx”).Sheets(1)

  114. Jon is complicated :( not work,

    and ADODB.Connection for copy ? mmm will work with images?

    Like this:
    **********************************************
    Private Sub CommandButton1_Click()
    Dim strArchivo As String, strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim arrData As Variant

    strArchivo = “C:\Libro1.xls”

    If Dir(strArchivo) = “” Then
    MsgBox “No existe el archivo en la ruta indicada.”
    Exit Sub
    End If

    strSQL = “SELECT * FROM [Hoja1$A2:A15]”

    Set cn = New ADODB.Connection
    cn.Open “DRIVER={Microsoft Excel Driver (*.xls)};” & _
    “DriverIdy0;ReadOnly=True;DBQ=” & strArchivo & “;”

    Set rs = New ADODB.Recordset
    rs.Open strSQL, cn, adOpenForwardOnly, _
    adLockReadOnly, adCmdText

    Workbooks(“Libro2.xls”).Worksheets(“Hoja1”) _
    .Range(“C3”).CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    *************************************************************

    Regards.

    Orison

  115. ADODB? You’re asking the wrong guy. But I think that will only extract numerical data, not any images in the Excel file.

    Maybe you could store the path and file name of the images (stored independently, not embedded in a worksheet). You can extract the addresses using SQL, then insert the images using their file names.

  116. Hi Jon,

    This is an excellent resource for VBA learners and I really like your illustrative examples. In addition, all the questions being asked and provided solutions are extremely good learning aids. I’m farily new (3 months) to VBA and stll picking up things. I hope you can help with my below query.

    I have some code which updates multiple pivot tables across multiple worksheets based on the last 8 columns of a dynamic range within each worksheet. The range row number and positions are always the same in each worksheet but an additional column is added each quarter for that quarters data.

    LAST is a function.

    My code is below:

    Sub Refresh_All_Pivots()

    On Error GoTo err_handler

    Application.ScreenUpdating = False

    Dim field1 As String
    Dim field2 As String
    Dim field3 As String
    Dim field4 As String
    Dim field5 As String
    Dim field6 As String
    Dim field7 As String
    Dim field8 As String

    Dim ws As Worksheet
    Dim Pt As PivotTable
    Dim pf As PivotField

    Dim rng As Range
    Dim lastcol As Long
    Dim LastRow As Long

    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    For Each Pt In ws.PivotTables

    Set rng = Range(“A4”).currentregion

    LastRow = Last(1, rng)
    lastcol = Last(2, rng)

    field1 = rng.Cells(1, (lastcol) – 7).Text
    field2 = rng.Cells(1, (lastcol) – 6).Text
    field3 = rng.Cells(1, (lastcol) – 5).Text
    field4 = rng.Cells(1, (lastcol) – 4).Text
    field5 = rng.Cells(1, (lastcol) – 3).Text
    field6 = rng.Cells(1, (lastcol) – 2).Text
    field7 = rng.Cells(1, (lastcol) – 1).Text
    field8 = rng.Cells(1, (lastcol)).Text

    Pt.SourceData = Range(“A4”).currentregion.Address(True, True, xlR1C1, True)

    Pt.DataPivotField.Orientation = xlHidden

    With Pt.PivotFields(field1)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field1
    .Function = xlSum
    End With

    With Pt.PivotFields(field2)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field2
    .Function = xlSum
    End With

    With Pt.PivotFields(field3)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field3
    .Function = xlSum
    End With

    With Pt.PivotFields(field4)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field4
    .Function = xlSum
    End With

    With Pt.PivotFields(field5)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field5
    .Function = xlSum
    End With

    With Pt.PivotFields(field6)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field6
    .Function = xlSum
    End With

    With Pt.PivotFields(field7)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field7
    .Function = xlSum
    End With

    With Pt.PivotFields(field8)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field8
    .Function = xlSum
    End With

    Pt.RefreshTable
    Errorhandling:

    Next Pt
    Next ws
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.ScreenUpdating = True

    Sheet1.Activate

    Exit Sub

    err_handler:
    MsgBox “One or more of the Pivot tables have experienced an error.” & vbCrLf & vbCrLf & _
    “Please look through the workbook to determine where the error has occurred.”, _
    vbOKOnly, “Error with Refreshing Pivots”

    Resume Errorhandling

    End Sub

    My problem is that I now have a workbook with a similar kind of format where I would like to refresh multiple pivot tables. However, in this workbook, within each worksheet, there are 3 pivot tables which use one data range as source data (same range across each worksheet), there is 1 pivot table which uses another range as its source data (same range across each worksheet), and another pivot table which uses another separate range (again the same range across each worksheet).

    What I would liek to do is instead of using:

    For Each Pt In ws.PivotTables

    Can I specify a particular group of pivot tables based on their name??

    E.g.

    Dim ptname As String
    Dim ptname2 As String
    Dim ptname3 As String

    Set ptname1 = ws.pivotables(PivotTable9, PivotTable6)
    Set ptname2 = ws.PivotTables(PivotTable5)
    Set ptname3 = ws.PivotTables(PivotTable8, PivotTable7, PivotTable4)

    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    For Each Pt In ws.PivotTables(ptname1)

    Set rng = Range(“specified separate range”).currentregion………….

    The above code gives me the following error message:

    Compile Error:
    “Method or Data member not found”

    whilst highlighting:

    .pivottables

    in the following line of code:

    Set ptname1 = ws.pivotables(PivotTable9, PivotTable6)

    Any help on how I can specify a group of pivot tables so that the source data on each individual worksheet can be set for each pivot table in that group and then loop through to a new group of pivot tables would be greatly appreciated.

  117. Carl –

    I might have set up your source data differently in the first case so all my data goes into the same column, but there is another column indicating which quarter the data belongs to. The source data becomes longer not wider as time goes on. There is a column field for the quarter, and there is a single data field. The column field would include only the last N entries: this is what you might need code for.

    For the second question, I’d use a Select Case block:

    For Each PT In ws.PivotTables
      Select Case PT.Name
        Case "PivotTable9", "PivotTable6"
          ' Do something relevant to pivot table 5
        Case "PivotTable8", "PivotTable7", "PivotTable4"
          ' You know the drill
        Case Else
          ' What to do... Tell user there's an unknown pivot table?
      End Select
    Next
  118. Dear Jon,

    I found your tutorial very helpful for some of my projects. There is, however, something I am struggling for several days already and I just can’t find the right way to do it.

    I have a simple pivot table with two fields in the row part and one in the data part (The columns and page parts are empty). Next to the grand total, there is a total for each of the pivot items of the first field.

    What I would like to do is select the row totals per pivot item display the data on a new sheet in the same workbook (for this I use the GetPivotData function). Subsequently this new sheet should be given the name of the respective pivot item. The problem is that the number of pivot items would change depending on the data.

    Could you advise me on how to deal with this issue?

    Many thanks in advance!

    Best regards,
    Petar

  119. Petar –

    If you’re looking for a programmatic approach, you may want to write a procedure that loops through each pivot item in a pivot field, then puts the name of the pivot item and its total into a new sheet, assigning the pivot item name as the sheet name.

    GetPivotData causes me to pull out my hair.

  120. Hi Jon,

    This is exactly what I want to do. But my basic VBA knowledge does not allow me to get the loop working right. I am getting messed up with the syntax.
    Would you suggest an example I might use as a guideline for my project?

    Thanks,
    Petar

  121. Petar –

    It helps to know the syntax you’re having difficulties with.

    The pivot table:
    Set pt = ActiveSheet.PivotTables(1)

    The pivot field:
    Set pf = pt.PivotFields(“field name”)

    Loop the pivot items:
    For Each pi In pf.PivotItems

    Name of the pivot item: pi.Caption or pi.Name or pi.Value

    Range containing the data value:
    pi.DataRange

    If it’s one cell, pivot item’s data value; if it’s multiple cells in a contiguous range, array of values:
    pi.DataRange.Value

  122. Hi Jon,

    Thank you for your reply.

    Below the part of the code where I want to loop through the pivot items of a particular pf (“AC Owner”) and display the row total for each pi in a separate sheet and name it after the pi name.

    I have s2 defined as worksheet, pt as pivottable, pf as pivotfield, pi as pivotitem, mycell as range.

    =================
    With s2

    If Weekday(Date) = vbFriday Then

    pt.PivotFields(“AC Owner”).Orientation = xlRowField
    pt.PivotFields(“AC Owner”).Position = 1
    Columns(“A:A”).EntireColumn.AutoFit

    Set pf = pt.PivotFields(“AC Owner”)

    For Each pi In pf.PivotItems
    pi.Name (HERE I GET A compile Error: Invalid use of property)
    Set mycel = pt.GetPivotData(“Customer Name”, “AC Owner”, pi.Name)
    mycel.Offset(0, 2).ShowDetail = True
    ActiveSheet.Name = pi.Name
    Next

    s2.Select

    MsgBox “Something…
    Else
    MsgBox “Something else…”
    End If
    End with
    ==================

    I get an error with pi.Name. I don’t know what I am missing in the code. Any help will be much appreciated.

  123. Hi Jon,

    It’s me again. I almost got it working. I went through the comments again and made some adjustments of the above code.

    Now it looks like this:

    ======
    With s2

    If Weekday(Date) = vbFriday Then

    pt.PivotFields(“AC Owner”).Orientation = xlRowField
    pt.PivotFields(“AC Owner”).Position = 1
    Columns(“A:A”).EntireColumn.AutoFit

    Set pf = pt.PivotFields(“AC Owner”)

    With pt
    If .ColumnGrand Then
    With .DataBodyRange
    Set rColumnTotal = .Rows(.Rows.Count)
    End With
    End If
    End With

    For Each pi In pf.PivotItems
    Set mycell = Intersect(pi.DataRange.EntireColumn, rColumnTotal.EntireRow)
    mycell.ShowDetail = True
    ActiveSheet.Name = pi.Name
    Next

    s2.Select
    ‘pf.Orientation = Hidden

    MsgBox “Something…
    Else
    MsgBox “Something else…”
    End If
    End with
    ======

    Everything works fine, except from selecting the grand totals per pi. Every time the loop runs, the grand total for all pi’s in the pf is selected (so basically the cell in the right bottom of the pivot).

    The issue is now with the way rColumnTotal is defined. How can I get this counting only the row grand totals per pi?

  124. Petar –

    You might not be selecting the appropriate ranges in your Intersect statement. I had the impression you wanted the total in the last column, not in the last row. If ColumnGrand is true, it means you have column totals in the bottom row.

  125. Jon,

    My pivot table looks liek this:
    =====================
    AC Owner | Name| Total
    ______________________
    AAA |zyw | 1
    ______________________
    —— | xyz | 2
    ______________________
    AAA Total |——–| 3
    ______________________
    BBB |zyw | 6
    ______________________
    —— | xyz | 2
    ______________________
    BBB Total |——–| 8
    ______________________
    Grand Total |——–| 11
    ______________________

    What the last posted code does now is getting the Grand total (11 in this example). What I want to do is to get the total of AAA Total (3) – select the cell and display the data into a new sheet. The same for BBB Total (8) and any other existing pivot items totals.

  126. Hi Jon,

    I’ve tried to incorporate a Select Case block into my code as you suggested, however, I now get a Run-Time Error ‘1004’ message at the following line of code:

    pt.SourceData = Range(“A4”).CurrentRegion.Address(True, True, xlR1C1, True)

    The error message reads as follows:

    “The Pivot Table field name is not valid. To create a PivotTable report, you must use data that is organised as a list with labelled columns. O fyou are changing the name of a PivotTable field, you must type a new name for the field.”

    My original code worked ok prior to the selct case block so I’m assuming it may have something to do with that?

    Please see below my new code with the Select Case block:

    Sub Refresh_All_Pivots()

    ‘On Error GoTo err_handler

    Application.ScreenUpdating = False

    Dim field1 As String
    Dim field2 As String
    Dim field3 As String
    Dim field4 As String
    Dim field5 As String
    Dim field6 As String
    Dim field7 As String
    Dim field8 As String

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField

    Dim rng As Range
    Dim lastcol As Long
    Dim LastRow As Long

    ‘ActiveSheet.PivotTables(“PvtVarianceRpt2”) _
    ‘.PivotFields(“Line”).CurrentPage = Str

    ‘——————————————————————————

    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    For Each pt In ws.PivotTables

    Select Case pt.Name

    Case “PivotTable27”, “PivotTable28”, “PivotTable29”

    Set rng = Range(“A4”).CurrentRegion

    LastRow = Last(1, rng)
    lastcol = Last(2, rng)

    field1 = rng.Cells(1, (lastcol) – 7).Text
    field2 = rng.Cells(1, (lastcol) – 6).Text
    field3 = rng.Cells(1, (lastcol) – 5).Text
    field4 = rng.Cells(1, (lastcol) – 4).Text
    field5 = rng.Cells(1, (lastcol) – 3).Text
    field6 = rng.Cells(1, (lastcol) – 2).Text
    field7 = rng.Cells(1, (lastcol) – 1).Text
    field8 = rng.Cells(1, (lastcol)).Text

    pt.SourceData = Range(“A4”).CurrentRegion.Address(True, True, xlR1C1, True)

    pt.DataPivotField.Orientation = xlHidden

    With pt.PivotFields(field1)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field1
    .Function = xlSum
    End With

    With pt.PivotFields(field2)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field2
    .Function = xlSum
    End With

    With pt.PivotFields(field3)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field3
    .Function = xlSum
    End With

    With pt.PivotFields(field4)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field4
    .Function = xlSum
    End With

    With pt.PivotFields(field5)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field5
    .Function = xlSum
    End With

    With pt.PivotFields(field6)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field6
    .Function = xlSum
    End With

    With pt.PivotFields(field7)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field7
    .Function = xlSum
    End With

    With pt.PivotFields(field8)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field8
    .Function = xlSum
    End With

    Case “PivotTable30”

    Set rng = Range(“F40”).CurrentRegion

    LastRow = Last(1, rng)
    lastcol = Last(2, rng)

    field1 = rng.Cells(1, (lastcol) – 7).Text ‘ not sure if the .value is required or not when specifying a string
    field2 = rng.Cells(1, (lastcol) – 6).Text ‘ not sure if the .text is required??
    field3 = rng.Cells(1, (lastcol) – 5).Text
    field4 = rng.Cells(1, (lastcol) – 4).Text
    field5 = rng.Cells(1, (lastcol) – 3).Text
    field6 = rng.Cells(1, (lastcol) – 2).Text
    field7 = rng.Cells(1, (lastcol) – 1).Text
    field8 = rng.Cells(1, (lastcol)).Text

    pt.SourceData = Range(“F40”).CurrentRegion.Address(True, True, xlR1C1, True)

    pt.DataPivotField.Orientation = xlHidden

    With pt.PivotFields(field1)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field1
    .Function = xlSum
    End With

    With pt.PivotFields(field2)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field2
    .Function = xlSum
    End With

    With pt.PivotFields(field3)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field3
    .Function = xlSum
    End With

    With pt.PivotFields(field4)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field4
    .Function = xlSum
    End With

    With pt.PivotFields(field5)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field5
    .Function = xlSum
    End With

    With pt.PivotFields(field6)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field6
    .Function = xlSum
    End With

    With pt.PivotFields(field7)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field7
    .Function = xlSum
    End With

    With pt.PivotFields(field8)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field8
    .Function = xlSum
    End With

    Case “PivotTable33”

    Set rng = Range(“F50”).CurrentRegion

    LastRow = Last(1, rng)
    lastcol = Last(2, rng)

    field1 = rng.Cells(1, (lastcol) – 7).Text ‘ not sure if the .value is required or not when specifying a string
    field2 = rng.Cells(1, (lastcol) – 6).Text ‘ not sure if the .text is required??
    field3 = rng.Cells(1, (lastcol) – 5).Text
    field4 = rng.Cells(1, (lastcol) – 4).Text
    field5 = rng.Cells(1, (lastcol) – 3).Text
    field6 = rng.Cells(1, (lastcol) – 2).Text
    field7 = rng.Cells(1, (lastcol) – 1).Text
    field8 = rng.Cells(1, (lastcol)).Text

    pt.SourceData = Range(“F50”).CurrentRegion.Address(True, True, xlR1C1, True)

    pt.DataPivotField.Orientation = xlHidden

    With pt.PivotFields(field1)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field1
    .Function = xlSum
    End With

    With pt.PivotFields(field2)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field2
    .Function = xlSum
    End With

    With pt.PivotFields(field3)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field3
    .Function = xlSum
    End With

    With pt.PivotFields(field4)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field4
    .Function = xlSum
    End With

    With pt.PivotFields(field5)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field5
    .Function = xlSum
    End With

    With pt.PivotFields(field6)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field6
    .Function = xlSum
    End With

    With pt.PivotFields(field7)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field7
    .Function = xlSum
    End With

    With pt.PivotFields(field8)
    .Orientation = xlDataField
    .Caption = “Sum of” & ” ” & field8
    .Function = xlSum
    End With

    Case Else

    MsgBox “Unrecognised Pivot table.” & vbCrLf & vbCrLf & _
    “Please check the workbook.”, _
    vbOKOnly, “Error with Refreshing Pivots”

    End Select

    pt.RefreshTable
    Errorhandling:

    Next pt
    Next ws
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.ScreenUpdating = True

    Sheet1.Activate

    Exit Sub

    err_handler:
    MsgBox “One or more of the Pivot tables have experienced an error.” & vbCrLf & vbCrLf &
    ‘”Please look through the workbook to determine where the error has occurred.”, _
    ‘vbOKOnly, “Error with Refreshing Pivots”

    Resume Errorhandling

    End Sub

    Do you have any ideas why the Select Else block would provide an error??

    Thanks in advance.

    Carl

  127. Why not give the pivot tables descriptive names, “ptSales”, “ptExpenses”, etc.?

    Why do you need to keep redefining the source data range of the pivots? What changes about the range? If you converted the range to a list/table, would this accommodate the change? If so, all you’d need to say is pt.RefreshTable, not the other 99% of this code.

    About the error: “The Pivot Table field name is not valid.” Are any of the cells in the first row blank? Or are they otherwise unsuited for use as a pivot field name?

  128. Jon, this code works fine in Excel 2003, but after saving to Excel 2007 it fails, any ideas?

    Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.PivotCell.Parent = “PivotTableOS” Then
    Worksheets(“TS Cost”).PivotTables(“PivotTableTS”).PageFields(“Customer (SCORE)”).CurrentPage = _
    Worksheets(“OS Cost”).PivotTables(“PivotTableOS”).PageFields(“Customer (SCORE)”).CurrentPage.Name
    End If

    If Target.PivotCell.Parent = “PivotTableOS” Then
    Worksheets(“Office Cost”).PivotTables(“PivotTableOFF”).PageFields(“Customer (SCORE)”).CurrentPage = _
    Worksheets(“OS Cost”).PivotTables(“PivotTableOS”).PageFields(“Customer (SCORE)”).CurrentPage.Name
    End If

    If Target.PivotCell.Parent = “PivotTableOS” Then
    Worksheets(“SQL Cost”).PivotTables(“PivotTableSQL”).PageFields(“Customer (SCORE)”).CurrentPage = _
    Worksheets(“OS Cost”).PivotTables(“PivotTableOS”).PageFields(“Customer (SCORE)”).CurrentPage.Name
    End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

  129. Jim –

    You don’t say what the error message was, or on which line it occurred.

    Don’t forget to explicitly invoke properties and methods, for instance, Target.PivotCell.Parent.Name

  130. Sorry Jon, here’s what I get…

    Run-time error ‘5’:
    Invalid proceedure call or argument. Clicking “Debug” highlights the following code…

    Worksheets(“TS Cost”).PivotTables(“PivotTableTS”).PageFields(“Customer (SCORE)”).CurrentPage = _
    Worksheets(“OS Cost”).PivotTables(“PivotTableOS”).PageFields(“Customer (SCORE)”).CurrentPage.Name

  131. Maybe use CurrentPage.Name before the equals sign?

  132. Hi Jon,

    I am pretty much a novice at VBA, reading up on my spare time…. But I am looking into simplifying a task that I normally do. I would think there’s a quick an dirty code to write, that I am not grasping yet.

    I have two pivot tables ( one on top of the other) on one of my spreadsheets that “refreshes” each time a data dump is created, which increases the number of rows the pivot tables read from.

    I would like to have the rows in between the two pivot table to be deleted, so that they are about 3 rows apart, each time after the PT refreshes… My template currently separates them by about 200 rows to allow for the refresh.

    Is there a way for VB, to calculate the number of rows it needs to delete; in order to leave just 3 rows, between the last row of the top PT and the top row of the bottom PT?

  133. Gus –

    First, you’d better insert enough rows before refreshing the pivot tables, or you’ll get warnings that pivot tables can’t overlap each other.

    How about keeping 1000 rows between the pivot tables, and hiding whatever rows you don’t need?

    Then after refreshing the pivots you can run code like this:

    Dim ptUpper As PivotTable, ptLower As Pivot Table
    Dim iRowFirstBlank As Long, iRowLastBlank As Long
    
    If ActiveSheet.PivotTables(1).TableRange1.Row < ActiveSheet.PivotTables(2).TableRange1.Row Then
      Set ptUpper = ActiveSheet.PivotTables(1)
      Set ptLower = ActiveSheet.PivotTables(2)
    Else
      Set ptUpper = ActiveSheet.PivotTables(2)
      Set ptLower = ActiveSheet.PivotTables(1)
    End If
    
    iRowFirstBlank = ptUpper.TableRange1.Row + ptUpper.TableRange1.Rows.Count
    iRowLastBlank = ptLower.TableRange2.Row - 1
    
    ActiveSheet.Rows.Hidden = False
    ActiveSheet.Rows(iRowFirstBlank + 3).Resize(iRowLastBlank - iRowFirstBlank - 2).Hidden = True
  134. You’re absolutely right, Jon, I am aware of the overlapping error message. Just wondering how difficult creating code that actually deletes the lines between the 2 Pivot Tables would be… Could I just substitute the part of your code that hides the rows with a delete statement…

  135. Gus –

    You could do this, as long as you made sure to insert enough rows before the next refresh. But I’m sure I would forget, then go through the warnings, etc.

  136. Hi Jon,

    Re my post on 25 february and your reply.

    My pivot tables are dynamic which is why I keep redefining the Pivot table Source data. An additional column is added for each month.

    With your help and some step by step testing I found that my calculations associated with my Last Cell function were not correct when sourcing a different data range meaning that I was referencing blank cells for my Pivot Table field names. I have now amended the code and it works fine.

    Thanks for your help.

    Carl

  137. Carl –

    Glad you’ve got it sorted out. Sometimes it takes a few painful iterations.

  138. Hi Jon, I’m beating my brains out with this one…..Do you see anything wrong with this. No errors, nothing, just doestn’t work for some reason.

    Option Explicit
    Dim mvPivotPageValue As Variant

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim wsOS As Worksheet
    Dim wsTS As Worksheet
    Dim wsOF As Worksheet
    Dim wsSQL As Worksheet

    Dim pt As PivotTable
    Dim pt1 As PivotTable
    Dim pt2 As PivotTable
    Dim pt3 As PivotTable
    Dim strField As String

    Set wsOS = Worksheets(“OS Cost”)
    Set wsTS = Worksheets(“TS Cost”)
    Set wsOF = Worksheets(“Office Cost”)
    Set wsSQL = Worksheets(“SQL Cost”)

    Set pt = wsOS.PivotTables(“PivotTableOS”)
    Set pt1 = wsTS.PivotTables(“PivotTableTS”)
    Set pt2 = wsOF.PivotTables(“PivotTableOFF”)
    Set pt3 = wsSQL.PivotTables(“PivotTableSQL”)

    strField = “Customer (SCORE)”

    If LCase(pt.PivotFields(strField).CurrentPage) LCase(mvPivotPageValue) Then
    ‘The Page Field was changed
    ‘Application.EnableEvents = False
    pt.RefreshTable
    mvPivotPageValue = pt.PivotFields(strField).CurrentPage
    pt1.PageFields(strField).CurrentPage = mvPivotPageValue
    pt2.PageFields(strField).CurrentPage = mvPivotPageValue
    pt3.PageFields(strField).CurrentPage = mvPivotPageValue

    Application.EnableEvents = True
    End If

    End Sub

  139. Jim –

    What happens if you step through the code?

  140. Hi Jon,

    I found that VBA does not play well with parentheses in the column name. I changed to brackets, and the code works fine. Thanks for your reply.

  141. With your examples above, where you have pivoted the “Years” field, how would you go about referencing a “Years” subtotal range? I’ve used pivotselect but I would prefer greater control…

    Thanks

  142. Hello,

    I’m very new to VBA but trying to write a macro to calculate the difference between one month to another. I need this to calculate only the subtotal rows. This is a snapshot of how it’s set up:

    Sum of SumOfamt_paid Month
    vend_desc billing_acct analysts inv_date February March

    I tried using the recorder 1st to start in cell A1, edit find total, offset 6 columns then calculate March less February. I can get it to work one time, but I need it to loop for every subtotal. The problem I’m having is trying to get it to not reference the vendor name in the subtotal so it will loop. Any help is greatly appreciated!

  143. Angie – Your description is not complete enough for me to comment.

  144. Hi Jon,

    Outstanding article, I used some of it already, but I seemed to have an issue which I can not find a solution for it in your article. My work project file is located here:
    http://www.mediafire.com/?h8ovuwkmw2w9t0x

    What I am trying to do (which is also outlined in my code comments) is format my Data Range and Total Range, but I am having a hard time selecting them. I do not have an X and Y setup like you, where you have Order Date on the Y axis and Years on the X axis.

    Instead, I have a column for PLAN, a column for PLAN TYPE, a Data Column and a Total Column. How do I select (or add to a range) just the Information for my Data column (not including the totals at the bottom) and the same thing for the Total Column.

    Basically, I want to find PT.DataBodyRange.(minus the last 2 rows).

    Hopefully this explanation makes sense!

    Thank you in advance,
    Kevin

  145. Jon,

    I solved my above question and used the following coding:

    Sub FormatSalesByProduct()

    Dim PT As PivotTable
    Dim PF1 As PivotField, PF2 As PivotField, PF3 As PivotField
    Dim PI1 As PivotItem, PI2 As PivotItem
    Dim ws As Worksheet

    SalesByProductToPrint.Activate ‘Activates the sheet by CodeName

    Set PT = ActiveSheet.PivotTables(1)
    Set PF1 = PT.RowFields(1)
    Set PF2 = PT.RowFields(2)
    Set PF3 = PT.RowFields(3)
    On Error Resume Next
    For Each PI1 In PF1.VisibleItems
    PI1.LabelRange.BorderAround _
    Weight:=xlMedium
    For Each PI2 In PF2.PivotItems
    Intersect(PI2.LabelRange, PI1.LabelRange.EntireRow).BorderAround _
    Weight:=xlMedium
    With Intersect(PF3.DataRange, PI2.LabelRange.EntireRow, PI1.LabelRange.EntireRow)
    .Borders(xlInsideHorizontal).Weight = xlThin
    .BorderAround Weight:=xlMedium
    End With
    With Intersect(PT.DataBodyRange, PI2.LabelRange.EntireRow, PI1.LabelRange.EntireRow)
    .Borders(xlInsideHorizontal).Weight = xlThin
    .BorderAround Weight:=xlMedium
    End With
    Next PI2
    Next PI1

    End Sub

    This allowed me to format the two columns which I couldn’t identify directly from your examples above, as my pivot was not laid out like any of yours!

    Great blog – keep up the good work!

    Thank you,
    Kevin

  146. Hello Jon, Hello Readers,
    I’ve found this article very useful to start understanding pivot manipulation via VBA.
    In my workbook, I have a table with 2 rows (let’s call them Row1 and Row2 ) and no column, but with the count of a value in the data range (count of Data1).
    I’ve tried to use the same methods shown in your example, but I cannot make them work properly.

    The simple code I’m working on is the following (there are two different selections, but none of them works):

    Dim pt As PivotTable
    Dim r As Range
    Dim i As Integer

    Set pt = ActiveSheet.PivotTables(1)
    Set r = Intersect(pt.PivotFields(“Row1”).PivotItems(“Value5”).DataRange.EntireRow, pt.PivotFields(“Data1”).DataRange)

    ‘Set r = pt.PivotFields(“Row1”).PivotItems(“Value5″).DataRange

    For i = 1 To r.Width
    MsgBox (r(i) & ” ” & i)
    Next

    1) If I use the intersect method to select the cells of R2 corresponding to a certain R1, the selection is fine, but if I assign this to a range, the range is way bigger than the selection highlighted on the screen, including cells after the ones I want.
    This happens both with a PageRange filter that without one.
    How can I select the correct range, that is the one that it is higlighted?
    2) Moreover, in my pivot I have a row of totals for each value in R1 (as if there was a total for 2003, one for 2004… in your example).
    Is it possible to select that total value?
    Because it doesn’t belong to the data range of a specific entry (e.g. pt.PivotFields(“Row1”).PivotItems(“Value7”).DataRange.Select), or at least to the part that is higlighted, because also in this case if I assign a range, it is bigger than the higligthed part.
    Thank you very much!

  147. Jon,

    I don’t have any questions for you thankfully as it seems you have your work cut out here! I just wanted to say thanks for an excellent resource page – I was stumped on some of this stuff, and was using code to identify the start and end of ranges based upon ‘last row/column containing a value’ to define the range limits.

    Obviously why do in 1 row of code what you can do in 235!!!

    Thanks again – my debugging is sooooo much simpler now – now, where’s that ‘Bookmark this page’ command…?

    Cheers,

    Chris

  148. Hello, I wonder if you can import data from a range of cells with ado, but
    only those cells that do not have a formula in the cell. Is that possible? a guide
    please.

    Regars

    Orison
    Lima – Peru

  149. Orison –

    As far as I know, ADO only looks at the values of cells, and has no knowledge of whether a cell contains an underlying formula.

  150. Hi John
    I not very expert with VB
    I don’t find the solution for my problem.
    1) My pivotable (PivotTable_CDL) stay into sheet VALORI
    2) Into sheet (Dashboard) I have a graphic e table that take the value from pivot (PivotTable_CDL)
    In this second sheet I insert into CELL :G1 the name CDL for apply filter into pivot and this it’s ok
    3) I don’t know to do for put a double selection on pivot table
    I apply the filter CDL so and more use another filter from other CELL where I put the value of the YEAR example 2011.
    With this value I want filter (into filter CDL) the (PivotTable_CDL) for the Item ANNO for value (2011-1) and (2011). Put year and year-1 of select pivot

    Sub up_pivot()

    Dim PvtTable As PivotTable
    Dim PvtField As PivotField
    Dim PvtItem As PivotItem
    Dim FilterCost As String

    Set PvtTable = Worksheets(“VALORI”).PivotTables(“PivotTable_CDL”)
    Set PvtField = PvtTable.PivotFields(“Cdl”)

    FilterCost = Worksheets(“Dashboard”).Range(“G1”)
    ANNO_A=Worksheets(“Dashboard”).Range(“h1”) ‘ex. 2011
    ANNO_DA = ANNO_A – 1 ‘ex. 2010 (2011-1)

    PvtField.ClearAllFilters
    PvtField.PivotFilters.Add Type:=xlCaptionEquals, Value1:=FilterCost
    ‘ .PivotFilters.Add Type:=xlDateBetween, Value1:=ANNO_DA , Value2:=ANNO_A

    End Sub

    ************************
    Second problem for printing
    Now the sub print all values from PivotTable_CDL (test the cell with value=2 for printing)

    The problem
    1)I want to set filter for the two value YEAR (ANNO of the pivot) , the same problem before but for every value of item CDL
    2) So the sub printing the layout with nothing from select year, so I want to not printing, bypass the value for CDL that haven’t value into PivotTable_CDL.

    Grazie

    Sub PVT_Stampa()

    Dim PvtTable As PivotTable
    Dim PvtField As PivotField
    Dim PvtItem As PivotItem
    Dim FilterCost As String

    Dim stampare As String
    Dim n_rec As Integer

    Set PvtTable = Worksheets(“VALORI”).PivotTables(“PivotTable_CDL”)
    Set PvtField = PvtTable.PivotFields(“Cdl”)
    ‘ FilterCost = Worksheets(“Dashboard”).Range(“G1″)
    FilterCost = ” ”

    On Error Resume Next ‘in data caso non trovato
    n_rec = 0
    stampare = Worksheets(“Dashboard”).Range(“N1”)

    If stampare = “2” Then

    For Each PvtItem In PvtField.PivotItems

    If IsEmpty(PvtItem.Value) = False Then
    PvtItem.Visible = True
    ‘*******************************+
    FilterCost = PvtItem.Value
    PvtField.ClearAllFilters
    PvtField.PivotFilters.Add Type:=xlCaptionEquals, Value1:=FilterCost
    ‘***************************************

    ‘ n_rec = PvtField.Function xlcount ????????

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Else
    PvtItem.Visible = False
    End If
    Next PvtItem

    End If

    End Sub

  151. Probably a silly question, but I’m new to the VBA programmming. I’m working on extracting data from multiple pivot tables and before doing that I have to standardize the layout of the multiple pivot tables. Right now, I can’t figure out an easier way to filter pivot items. The pivot field (page field) is Coverage Date and it has about 12 pivot items and I only need 4 of them. Any easier way then visible.false method?

    Thank you in advance

  152. AnC –

    Visible.False is pretty much how you do it. Inside a loop it’s easy enough.

  153. Hello i have a question/problem. I hope you can help me. I really need that (for work).

    It’s about Makro, VBA, Pivot

    i have one window (file) called: “pivot.xlcx” – thats my finished pivot table;
    A1 “Cat” B1 “euro”
    A2 “ccc1” B2 “5€”
    A3 “ccc2” B3 “10€”

    Cat stands for Categorie.
    I am able to press on plus at A2 “ccc1” or at A3 “ccc2” so it opens; and looks like that:
    A1 “Cat” B1 “euro”
    A2 “ccc1” B2 “5€”
    A3 “1a” B3 “2€”
    A4 “1b” B4 “3€”
    A5 “ccc2” B5 “10€”
    A6 “2a” B6 “10€”

    Thats my Pivot table. I Input the dates. So it is possible that the order-number changes. Because my pivot is automaticly sorted by B1 “euro”. e.g.

    A1 “Cat” B1 “euro”
    A2 “ccc2” B2 “10€”
    A3 “ccc1” C3 “15€” – new sum, new order.

    ok so far?

    ————–

    Now i have an other window (File) called “final.xlcx”;
    A1 “Cat” B1 “euro”

    Now i want a makro that i can use and it will fill my dates like that automaticly:

    A1 “Cat” B1 “euro”
    A2 “1a” B2 “2€”
    A3 “1b” B3 “3€”

    nothing more. (no categories!)

    Is it possible?

    Can you please rebuild this one and give me the result vba code?
    Would be really nice!

    Greetings
    max

  154. Jon:

    I found your site a couple of years ago, mainly because I was looking for a Cluster-Stack chart methodology, which you provided in a very useful tutorial. I keep referring back to this site often since then, mainly as a way of finding non-standard solutions. However, I am extremely impressed and thankful that you have put together such an outstanding piece of information in this tutorial. This ought to be part of any VBA textbook…

    Thanks a lot

    Edwin

  155. Hello,

    Would you be able to provide a snippet of code that will simply copy an entire pivot table and paste its values and formatting over itself? The formatting does not paste in Excel 2007.

    Many thanks,

    Jeff

  156. John,This is alekya.
    i am very new to this VBA coding.Please help me out how to collate different sheets of data placed in different folders and make the pivots for them based on the criterias assigned and its about callcenter metrics related dashboard.Daily i am doing manually am i automate it…

    Please suggest me…as i observed a couple months your not posting comments

    please help me out from this

    Thanks in advance

    Alekya

  157. Hi Alekya,

    Have you tried using the ‘Record Macro’ option to automate some of your actions?

    Carl

  158. Alekya –

    Yours is not a simple question or clarification related to this article. It is a pretty large project, and well beyond the scope of the article and beyond what anyone could answer in less than several hours.

    You’ll have to figure out the parts of your project, do some web searches, and follow up with some questions on a forum. Read Getting Answers For Your Excel Questions for guidelines.

  159. Orison San Juan says:

    Dear Jon

    I have a xlsm file with macros in excel which has VBA code to generate some estimates.

    The problem is that when you edit text in cells that are part of a calculation of the formulas I have in VBA I can not do an undo, then
    The data are not specific to the excel but are generated by VBA code, is there any way to delete setback when a data or more data?
    Just as when you write several cells and use the undo and I excel itself back or forward but I do not know if I let myself understand.

    Regards,

    Orison San Juan

  160. How do I reference a pivot, by using the pivot name, rather using the cell address in getPivotData?

  161. Valentin –

    Not hard (when you know how):

    pt.TableRange1.Offset(, pt.TableRange1.Columns.Count).Resize(, 1).Select

    Column to right of pivot table

  162. Kuman –

    Documentation for GetPivotData says you need “a reference to any cell, range of cells, or named range of cells in a PivotTable report”. The pivot table name will not work. You could name the pivot table’s table range 1 or even just its top left cell with the pivot table name, and use that.

    I’ve generally found GetPivotData to be more painful than useful, though I’ve never usually needed the functionality it’s best for.

  163. I might sound really stupid now but how can i select for example Feb in the 2004 part in the really last example?

  164. Timo –

    I’d use something like

    Intersect(pt.PivotFields(“Order Date”).PivotItems(“Feb”).DataRange, _
        pt.PivotFields(“Years”).PivotItems(“2004”).DataRange).Select

    This works in the crosstab arrangement, when months are in the rows area and years in the columns area (or vice-versa), and also in the list arrangement, when both months and years are in the same area.

  165. Hi Jon

    I have a query regarding filtering. I have a download from a CSV file of a load of data covering a number of days’ data which I summarise in a Pivot Table with the Report Filter set as Date. I am trying to create a macro which enables the user to select just one date in order to produce a chart of that day’s activity. I have used the Macro recorder which allows me to select a date from the pivot list but hard codes the date, in this case “20/03/2012”. In order to create a dynamic choice of date, I have created a value SelDate, but substituting this for “20/03/2012” in the code just gives the dreaded “Run Time Error ‘1004’ – Application-defined or object defined error”. Can you give me an idea as to what’s going wrong here? The code is as follows:

    Dim SelDate As Date

    SelDate = InputBox(Prompt:=”Enter Date in Format DD/MM/YYYY”, Title:=”Now…”)

    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Date”).ClearAllFilters
    ‘ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Date”).CurrentPage = _
    “20/03/2012”

    ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Date”).CurrentPage = SelDate

    Many thanks in advance for any help.

    Regards

    Gus

  166. Gus –

    You have to be careful of your formats. Are the dates in the pivot table actual dates? If so, they will not match up with a text value like “20/03/2012”. You might find that DateValue(“20/03/2012”) works.

    You could pause the code before the commented out line, and run this from the Immediate Window (copy paste in one line, then press Enter):

    For Each pi In ActiveSheet.PivotTables(“PivotTable1″).PivotFields(“Date”).PivotItems : ? pi.caption, CLng(pi.caption) : Next

    This will list the items in the Date field, and tell you if Excel thinks they are dates or text: there will be an error if you try to convert text to a long using CLng.

  167. Jon

    You are right – the date in the Pivot Table needs a string value in the macro, not a date value to work. Many thanks for your help.

    Regards

    Gus

  168. Me again, Jon. If you’ve got multiple columns in your databodyrange, do you know whether you can select a particular column by name or index?

    I know I can use Sheet1.PivotTables(“Scatterplot”).databodyrange.select to select the entire data area, but I’d like to individually reference the columns within it so I can write their address to a named range.

    I’ve got one called Sum of RETURNS_VOL and another called Max of MAX_AGE (Years).

    Currently I’m getting their addresses like this:
    sX = Sheet1.PivotTables(“Scatterplot”).DataBodyRange.Resize(, 1).Address
    sY = Sheet1.PivotTables(“Scatterplot”).DataBodyRange.Offset(, 1).Resize(, 1).Address
    …but it strikes me that:
    1) there must be a better way, and
    2) there’s unlikely a better way that Jon hasn’t had to crack.

    Any ideas?

  169. Ahhh…after much experimentation, I see you can select a particular column from the RowRange like this:
    pt.PivotFields(“RETURNS_VOL”).datarange.select

    But if you’ve dragged it into the data area, then you’ve got to prefix the name of the pivotfield with “Sum of” or “Average of” or whatever other name you or Excel might have assigned it with, like this:
    pt.PivotFields(“Sum of RETURNS_VOL”).datarange.select

    So from here it should be fairly easy to write some code that cycles through each visible pivotfield in my pivot table, and add a new named range that points to it, so that I can have those names dynamically feed some charts.

    All this just so I can avoid having a @#$%ing pivot chart appear.

  170. This is a fantastic tutorial, thanks!

  171. @Jeff Weir. I’m engaged in a similar process of creating named ranges that refer to pivot table areas. In regards to the data fields, this fact might simplify things:

    pt.DataFields(“Sum of RETURNS_VOL”).SourceName = pt.PivotFields(“RETURNS_VOL”).SourceName

    Interestingly, if I declare ptfField as a PivotField, when I cycle through all PivotFields like:

    For Each ptField in pt.PivotFields

    … it never finds pt.PivotFields(“Sum of RETURNS_VOL”) , even though I can refer to it as such. But if I change it to:

    For Each ptField in pt.DataFields

    … it does find “Sum of RETURNS_VOL”

    At least, I think so. Confusing! Great to have this excellent tutorial here and still generating comments.

  172. @Doug. Not sure if it’s interesting to you, but I had a crack at writing routine that stores visible pivotitems and their associated pivotfields as:
    1. an array of arrays (i.e. jagged array)
    2. A collection nested within a collection
    I haven’t yet worked out how to do it with dictionaries.

    Here’s the jagged array code:

    Sub PT_FilterItems()


    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim strPivotitems As String
    Dim iFields As Integer
    Dim iItems As Integer
    Dim varPivotInfo As Variant
    Dim varFields As Variant
    Dim varItems As Variant

    Set pt = ActiveSheet.PivotTables(1)

    ReDim varFields(0)
    ReDim varPivotInfo(0)
    ReDim varItems(0)

    For Each pf In pt.VisibleFields
    Select Case pf.Orientation
    Case xlColumnField, xlRowField, xlPageField
    If pf.Name “Data” Then

    ReDim Preserve varFields(iFields)
    varFields(iFields) = pf.Name

    ReDim Preserve varPivotInfo(iFields + 1)
    varPivotInfo(0) = varFields
    If pf.AllItemsVisible = True Then ‘ No items are hidden, so no need to iterate through them to see which is visible
    ReDim varItems(0)
    varItems(0) = “All”
    varPivotInfo(iFields + 1) = varItems
    Else:
    For Each pi In pf.PivotItems
    If pi.Visible = True Then
    ReDim Preserve varItems(iItems)
    varItems(iItems) = pi.Name
    iItems = iItems + 1
    End If
    Next pi
    varPivotInfo(iFields + 1) = varItems

    ‘Clear the Items array, and reset Items counter back to zero
    ReDim varItems(0)
    iItems = 0
    End If
    iFields = iFields + 1
    End If
    Case xlDataField, xlHidden
    ‘Do nothing…we just ignore these
    End Select
    Next pf

    ‘Dump the data to the immediate window, to prove this worked:
    For iFields = 0 To UBound(varPivotInfo(0))
    Debug.Print “PivotField: ” & varPivotInfo(0)(iFields)
    For iItems = 0 To UBound(varPivotInfo(iFields + 1))
    Debug.Print varPivotInfo(iFields + 1)(iItems)
    Next iItems
    Next iFields

    End Sub

    Here’s the collection approach:

    Sub PT_Collection()
    Dim pf As PivotField
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim i As Integer
    Dim j As Integer
    Set pt = ActiveSheet.PivotTables(1)
    Dim colPT As Collection
    Set colPT = New Collection
    Dim colPF As Collection
    For Each pf In pt.VisibleFields
    Set colPF = New Collection
    For Each pi In pf.PivotItems
    colPF.Add pi
    Next pi
    colPT.Add colPF, pf.Name
    Next
    For i = 1 To colPT.Count
    'Here is where I would print the PivotField names, but you can't retrieve them with a collection.
    'I would probably have to store them in another array dimension, so I can retrieve them
    ' i.e. store a pivotitems list (as a collection) and the pivotfield name (as a collection of 1 string) in a jagged array, and store that jagged array in a pivotfields collectio
    For j = 1 To colPT.Item(i).Count
    Debug.Print colPT.Item(i).Item(j)
    Next j
    Next i

    End Sub

  173. @ Jeff Weir, thanks for posting the code. I’ll give it a spin.

  174. No problem. Probably worth mentioning that the reason that I want to store PivotTable items in dictionary objects when they exist as collections already. Among other things, I want to be able to create ‘Custom View’ functionality that works for pivottables, so that the user can save their newly filtered pivottable, and at any time easily return to it, filters already in place.

    For anyone else reading along who’s not familiar with custom views, as per http://www.ozgrid.com/Excel/AutoFilters_2.htm custom views allow you to do this for autofilter collections by uing View>Custom Views and click the Add button (provided you don’t have any excel 2007/10 tables in your document – for some reason these conflict with the Custom View functionality, which is a damn stupid move on part of MS) . Give your view a name and make sure you have a tick next to Hidden rows, columns and filter settings. This is a must to retain all the settings. Click OK. You can then call up your filtered list as a Custom View by going to View>Custom Views, clicking on the name of your view and selecting the Show button.

    I want to do something similar with pivottables. So what I want to do is save my pivottabel settings somewhere (likely a hidden sheet), so that I can recreate it at the push of a button. I think the jagged array approach is going to be the best way to do this in terms of flexibility .. I can easily add further dimensions to hold other data regarding pivotfields, such as hidden items, number formats, whatever.

    Whereas with the collection/dictionary approach I would require a separate collection/dictionaries for each subsequent type of attribute, as far as I know. Also with collections, I don’t believe you can retrieve the key, so I won’t neccessarily know which set of filter items go with which pivot field. Although I could easily get around this by storing the subdictionary (i.e. the one that contains the pivotitems) in say the 2 element Variant Array; with the field name stored in Variant_Array(0) and the dictionary stored in Variant_Array(1).

    Note that this is a dictionary stored within a jagged array stored within a dictionary!

    Also, in case anyone is interested, here’s my code to churn out named ranges from pivotfields:

    Sub Make_Names()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim wks As Worksheet

    Set wks = ActiveSheet
    Set pt = wks.PivotTables(1)

    For Each pf In pt.VisibleFields

    With ActiveWorkbook.Names
    .Add Name:=Fix_Name(pf.Name), RefersTo:="=" & pf.DataRange.Address
    End With
    Next pf

    End Sub

    Public Function Fix_Name(sName As String) As String
    ' This code was from Craig Hatmaker. Google 'Beyond Excel' for more info
    ' Description:Conforms a string so it can be used as a name

    ' Parameters: sName String to be conformed

    ' Example: sColumnName = Fix_Name("1st deposit %")

    ' Date Ini Modification
    ' 11/02/10 CWH Initial Programming
    ' 11/20/10 CWH Used "Like" operator

    'If Not DebugMode Then On Error GoTo ErrHandler
    Fix_Name = sName

    Dim i As Integer

    'Substitute special invalid characters w/standard abbreviations
    sName = Replace(sName, "#", "_NUM")
    sName = Replace(sName, "$", "_AMT")
    sName = Replace(sName, "%", "_PCT")
    sName = Replace(sName, "-", ".")
    sName = Replace(sName, ",", "-")
    sName = Replace(sName, " ", "_")

    'Get rid of all other illegal characters
    i = 1
    Do While i <= Len(sName)
    If Not Mid(sName, i, 1) Like "[A-Z,a-z,0-9,.,_,\]" Then _
    sName = Left(sName, i - 1) & Right(sName, Len(sName) - i)
    i = i + 1
    Loop

    'First Character cannot be numeric & result cannot look like cell ref.
    If IsNumeric(Left(sName, 1)) Or sName Like "[A-Z]#" Then _
    sName = "_" & sName

    Fix_Name = sName

    ErrHandler:

    If Err.Number 0 Then MsgBox _
    "Fix_Name - Error#" & Err.Number & vbCrLf & _
    Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

    End Function

  175. @Doug…Ha…I didn’t realise you were the person behind yoursumbuddy, and that you’ve just posted code to do the same over at your blog. Doh!

  176. @ Jeff, I forgot about PivotTable.VisibleFields, which you use here and which doesn’t cause me the same problem with DataFields that I mentioned above and in my post.

  177. Hi, reading the post on Pivot tables now realize that this is something I could use to great effect.

    However I need some help please, the code below is recoreded and I have modifid the way it renames the sheets, after selecting the data in each cell in the Piviot table Range in the totals column on PT.

    The problem that I have the report I run the Macro on changes every time its run and errors occurs on the Pt.RowRange this may have 10 row or 20 rows depending on the data.

    Is the a away to do the same function that I have recorded and not stop the Macro from working by selecting all the cells in column B and not include the grandtotal row?

    Your guidince would be appriciated.

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “Furniture Monthly Overstock!R1C1:R653C14″, Version:=xlPivotTableVersion10). _
    CreatePivotTable TableDestination:=”Sheet1!R3C1″, TableName:=”PivotTable1” _
    , DefaultVersion:=xlPivotTableVersion10
    Sheets(“Sheet1”).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Site”)
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables(“PivotTable1”).PivotFields(“Product Code”)
    .Orientation = xlRowField
    .Position = 2
    End With
    ActiveSheet.PivotTables(“PivotTable1”).AddDataField ActiveSheet.PivotTables( _
    “PivotTable1”).PivotFields(“Product Code”), “Count of Product Code”, xlCount
    RANGE(“B5”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B6”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B7”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B8”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B9”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B10”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B11”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B12”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B13”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B14”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B15”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B16”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B17”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B18”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B19”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B20”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B21”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B22”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B23”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B24”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    Sheets(“Sheet1”).Select
    RANGE(“B25”).Select
    Selection.ShowDetail = True
    ActiveSheet.Name = “SITE ” & RANGE(“E2”).Value
    ActiveWindow.TabRatio = 0.8
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets(“Furniture Monthly Overstock”).Select
    Sheets(“Furniture Monthly Overstock”).Move Before:=Sheets(1)
    Sheets(“Sheet1”).Move Before:=Sheets(2)

  178. Thank you!
    It is beautiful.
    Rona

  179. This is awesome tutorial! I simply loved it.. thanks!

  180. HI Jon. I’ve noticed that as soon as you move a pivot to a different sheet, the object reference to it in VBA goes out of scope. For instance, try this on any sheet with a pivot table in it:

    Sub CopyTableRange2()

    Dim pt As PivotTable
    Dim wksNew As Worksheet

    Set pt = ActiveSheet.PivotTables(1)
    Debug.Print pt.Name
    Set wksNew = Worksheets.Add
    pt.TableRange2.Cut wksNew.Range("A1")
    Debug.Print pt.Name

    End Sub

    You get an error on that 2nd debug.print statement.

    At first, i thought it was because you were not directly copying the pivottable object. I discovered there was a .location property that was introduced in Excel 2007 that lets you move the pivot directly, like this:

    Sub CopyViaLocation()

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    Debug.Print pt.Name
    pt.location = "" 'an empty string makes Excel move the pivot to a new sheet
    Debug.Print pt.Name

    End Sub

    …but moving it to a new sheet still throws an error on that 2nd debug.print statement. And padding out that empty string with a real sheetname and cell reference makes no difference either:

    Sub CopyViaLocation_Qualified()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim wksNew As Worksheet

    Set pt = ActiveSheet.PivotTables(1)
    Debug.Print pt.Name
    Set wksNew = Worksheets.Add
    wksNew.Name = "TryThis"
    pt.Location = "TryThis!A1"
    Debug.Print pt.Name
    End Sub

    Interestingly, you can move it within the same sheet using this method without getting an error:

    Sub CopyViaLocation_SameSheet()

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    Debug.Print pt.Name
    pt.location = "B10"
    Debug.Print pt.Name
    pt.location = "d10"
    Debug.Print pt.Name
    End Sub

    This is weird. I don’t want the pt to go out of scope, because I’m half way through iterating through a whole bunch of visible fields. The reason I move it is that I want to alter it, and need to make sure I don’t overwrite any data a user might have put around the outside of the pt.
    I guess I can just move it to ZZ1 on the same sheet, do my thing, then move it back. But this had me scratching my head big time. You come across this issue?

    Cheers

    Jeff

  181. Jeff –

    If you move an object from place to place around its worksheet, it’s the same object. When the object is moved to a different sheet, it is actually deleted from the original sheet and a new copy of it is created on the new sheet. You have to use an elaborate system of Set MyObject = NewObject commands to keep track of the object. This goes for pivot tables and charts, and probably other objects as well.

  182. Cheers Jon. Yeah, that’s what I thought. Problem is I’m moving the pt in the middle of a For Each pf in pt.pivotfields loop. Your suggestion creates a new object – and he original pivottable object still goes out of scope – which breaks the loop.

    So I’ll just move it to a vacant part of the same sheet and do the fiddling with it there, then move it back.

    By the way, the new blog theme looks good but it’s REALLY annoying to have to click the Show More Recent Comments button 7 times just to get to the most recent comment.

  183. Jeff –

    I was going to suggest using a copy of the pivot table on the new sheet, so you knew how large a range it covered, but that would require making all the changes twice. Inefficient.

    I was also getting annoyed at all the clicking, so I’ve changed the setting to show 100 comments per page instead of 40.

  184. Hi Jon, just looking for some advice as struggling a little bit!

    Imagine that your first PT above has a sub-total row running right through it for the first 6 months of the year. I would like to select the two separate data regions on either side and apply color scales to those cells only, i.e. not the subtotal row

    So we are talking about two separate ranges within the 2003-2005 data region without the row subtotal or the col grand total

    In my actual example it’s not a 50:50 spilt so I can’t simply just divide the databodyrange in half to ascertain two separate ranges

    Any suggestions would be much appreciated

  185. Anthony –

    I provided a subtotal by including another pivot field (“Code”) with multiple pivot items (“A” and “B”), with subtotals for the field.

    This code:

    With ActiveSheet.PivotTables(1).PivotFields("Code")
        Union(.PivotItems("A").DataRange, .PivotItems("B").DataRange).Select
    End With

    … selected this region:

    Select Pivot Table Data Body Range Without Subtotals

  186. Hiya Jon, many thanks
    I arrived at a solution in a far more convoluted manner based on searching for the text ‘total’ and ‘grand total’ in col A. Probably 20 lines of code as opposed to 2!
    Would it be possible to take your solution a step further / make it more flexible so that I dont have to specifically refer to items A & B, i.e. I always know there will be two data sections but don’t know what the items names may be called?
    The overall aim of this is so I can reapply color scales only to those sections of data but it can flexible enough to be applied to different pivots where I know subtotal rows exist. I don’t want color applied to the totals and data as it destroys the effectiveness
    Unfortunately as the pivot changes, e.g expanding to the right it’s necessary to reapply the color scales
    I suppose a dynamic pivot color scale applicator is where I’m trying to get to

  187. Ps it may sound the trivial but the whole success of the dashboard app I’m building is based on color scales being effective as the end user has bought into this feature big time and prefers the heat map style presentation as opposed to any other method like graphs
    As I’ve about a 100 different pivot views of the data to construct with pivots of varying layout and which will expand to the right over time I’m looking to build a color scale applicator tool I can rely on 100%!

  188. Or to simplify further I want the ability to identify a range being:
    ‘DataRange NOT including subtotal rows or cols’
    This will most often be non-contiguous in my circumstances

  189. Anthony –
    You’ll have to work out the details, but you could do something like (pseudocode)
    For Each pf In pt.RowFields
    test pf.DataRange.address to determine which pivot field to use. Then
    For Each pi In pf.PivotItems
    to get the pivot items, and use a loop to Union them into a discontiguous range.
    I thought you might have been able to test the PivotCellType of the cells in the pivot table, and include only xlPivotCellValues, but in a quick test, cells in subtotal and grand total rows and columns are of type xlPivotCellValues, not xlPivotCellSubtotals or xlPivotCellGrandTotal. These only include cells in the row or column ranges that indicate subtotals or the grand total. You could still loop through the row and column areas and use these to exclude rows and columns, but it’s less straightforward than I would have expected.

  190. Think I’ve arrived at a generic solution based on yours for my requirements:

    Sub PivFormatDataRange()

    Dim pt As PivotTable
    Dim rPivFormat As Range

    Set pt = ActiveSheet.PivotTables(1)
    Set rPivFormat = Application.Union( _
    pt.PivotFields(1).PivotItems(1).DataRange, _
    pt.PivotFields(1).PivotItems(2).DataRange)

    rPivFormat.Select
    [now apply color scales]

    End Sub

    Wasn’t aware that a Pivot Item has a Data Range property so thanks again and apologies for swamping your blog!

  191. Instead of

    rPivFormat.Select
    Selection.(formatting steps)

    format the range directly without selecting it:

    rPivFormat.(formatting steps)

    This would run a bit more quickly.

  192. Hi John,
    I just stumbled accross this gem of yours while working on a client problem. Awesome! I’ll simply add my accolades to the pile :- )

    Many thanks,
    Lukas

  193. Jon Peltier uses VBA to test a condition and apply foitmtarng instead of using Excel’s CF Function. Pivot Table Conditional Formatting with VBA | Peltier Tech Blog | Excel Charts I’d be glad to help you adapt either of those examples, or develop alternative code if those don’t

  194. Hi Jon, I stumbled upon your blog and find your information very helpful. I need your help, though, in:
    1) creating four columns after the Grand Total column labeled “Current Year YTD”, “Previous Year YTD”, “Most Recent 12 Months” and “Prior 12 Months”
    2) For each row in Table 1 (repeat for Tables 2, 3, etc), sum up the column values based on a specific number of columns
    I have limited vba knowledge. Please help.
    Thank you!

  195. Paul –

    This is a little off-topic from the article above. Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.

  196. I’ll simply add my words of appreciation for this tutorial: Awesome!

  197. Very useful explanation which I frequently use. Thank you!!!!

  198. This is great stuff. I’m currently using some of the code in this post to custom format some pivot tables that I produce. What would be the code if I wanted to select the Column Grand Totals so that I can bold the totals and the label?

  199. Absolutely a fantastic tutorial. I’ve been trying to figure this out for several weeks to no avail. Reading your tutorial was like being in a dark room and having someone turn on the lights. THANK YOU

  200. I’m trying to use the complex range selection in Excel 2010, but here it seems it does not work anymore.
    I have tried it in an Excel 2003 set-up with the same vba coding and there it works.
    I assume with the changed pivot tables in Excel 2013 your vba coding doesn’t work anymore. Do you have another suggestion to make such a selection? I was wondering whether it’s possible to retrieve the row value that is shown in a pop-up when hoovering over the pivot items.

  201. Karel –

    I haven’t looked into this recently, but I have been frustrated by Excel 2010’s insistence on not using the Tabluar pivot table layout by default. I always have to go back in and change it, usually after wasting a little time on the table. I assume some of the other layouts, with labels for different fields being combined into single cells, causes errors in some of this code.

  202. Thanks Jon,
    Just this morning I figured out that it had to do with the way the pivot table is being presented.
    Changing the report layout to a tabular form solves this issue in Excel 2010

  203. Hi Karel –
    Thanks for validating my guess.

  204. I am VERY new to VBA programming and I have some Basic, Fortran and C++ training from about 15 years ago. In addition, I currently use SAS and have have used STAT and SPSS in the past. I am trying to “automate” some regular reporting that I have to do for work. Basically all I am trying to do is copy and paste the values from a pivot into cells on another sheet that have been linked to graphs. I figure out how to do that.

    What I want to do now is figure out how to use the value in the pivot page filter to determine which sheet in the workbook and which location on the sheet the data gets pasted to. In my code below I am selecting a cell and changing the cell color based on the value of a particular pivot page filter being equal to all. I am just doing this as a test of my if statement. Once the if is working I will use it to assign a value to a variable. In the corrent format the if statement functions properly with a simple conditional statement, but not when the pivot table is involved.

    Dim rValues As Range
    Dim colNames As Range
    Dim rowNames As Range
    Dim pt As PivotTable
    Dim PvtItem As PivotItem
    Dim sheetName As String

    Set pt = Sheets(“Pivot Table”).PivotTables(1)
    Set rValues = pt.DataBodyRange
    Set colNames = Intersect(rValues.EntireColumn, pt.ColumnRange.EntireRow)
    Set rowNames = Intersect(rValues.EntireRow, pt.RowRange.EntireColumn)

    Sheets(“Pivot Table”).Select
    If (pt.PivotFields(“Merck Accessible 18 MAB Market”).PivotItem.Value = “(ALL)”) Then
    Sheets(“Global Charts”).Select
    ActiveSheet.Range(“F9”).Interior.Color = RGB(0, 255, 255)
    End If

    Thanks for your help!

  205. Does the code stop at a particular line? What error message do you receive?

  206. Jon,

    The code stops here:

    If (pt.PivotFields(“Merck Accessible 18 MAB Market”).PivotItem.Value = “(ALL)”) Then

    and i get “Object doesn’t support this property or method”

    I forgot to include that I am working in excel 2010.

  207. Always helps to check the Object Browser.
    A PivotItem is one of the choices you have for the value of a PivotField.
    Try:
    If (pt.PivotFields(“Merck Accessible 18 MAB Market”).DataRange.Value = “(ALL)”) Then

  208. Jon,

    When I try that I get a compile error, “Expected: list separator or )”. The word Accessible, in the line below, is highlighted when I get the error.

    If (pt.PivotFields(“Merck Accessible 18 MAB Market”).DataRange.Value = “(ALL)”) Then

    Emily

  209. Make sure everything is spelled correctly, and that the quotes are real double quotes, and not the curly quotes that blog software enforces.
    (I tested this with a dummy pivot table that had no spaces in the pivot field name, which shouldn’t matter.)

  210. Thanks Jon! You are really amazing. I did have the curly blog quote as you suggested. Now, my if statement doesn’t trigger an error but the code within the if-statement doesn’t get executed. do you have any thoughts?

    Sheets(“Global Charts”).Select
    ActiveSheet.Range(“F9”).Interior.Color = RGB(0, 255, 255)

  211. Emily –
    Put a breakpoint in the code. Does the code actually run through the line that you want executed? What is the value of pt.PivotFields(“Merck Accessible 18 MAB Market”).DataRange.Value? Can you manually run this from the Immediate Window:
    ActiveSheet.Range(“F9″).Interior.Color = RGB(0, 255, 255)?

  212. Can you give some VBA examples or reference using PowerPivot pivottables? For an example for this one :
    Pivot Item DataRange
    pt.PivotFields(“Years”).PivotItems(“2004”).DataRange.Select.
    Thanks in advance

  213. I have not yet done anything meaningful with PowerPivot. Do your own exploring, with the Object Browser, the Immediate Window, and a good web search tool.

  214. Hi Jon – thanks again for such an invaluable resource – I’ve used this to great effect on a survey to ensure consistency of information across multiple pivot tables on a single worksheet (60+ pivots).

    Now I’m trying to introduce databar formatting to each pivot table to allow easy recognition of the winning value in each single filed pivot – e.g.:
    Pivottable Name: 1.4M
    Page Filters: Job; Dept
    Row Field: 1.4M (each pivot table is named after the question number it reports on for consistency)
    Values: Count of 1.4M, relabelled as ‘Freq’

    Job (All)
    Dept (All)
    ———————————
    Row Labels Freq
    Short 13
    Medium 10
    Long 3
    No Response 1
    Grand Total 27
    In the examples you have given, selecting the ‘DataBodyRange’ will include the GrandTotal, which I don’t want to do – instead I just want to apply the databar formatting to just the 13 thru’ 1 values – but I can’t see to identify the object level range to select – the rest of the code works a dream, I just can’t tie down the data range excluding the total row (I don’t want total’s switched off).

    Thanks,

    Chris

    For anyone else who wants it, here is the code block that works, excluding the indicated line to select the data range:
    Sub ColorPivot()
    On Error GoTo err_ColorPivot
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim kolor As Integer

    Cells.FormatConditions.Delete
    ‘change the settings
    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    pt.PivotItems.DatabodyRange.Select ‘<===== this line is broken, but does work if using pt.DataBodyRange.Select

    Select Case Left(pt.Name, 1)
    Case 1
    kolor = vbBlue
    Case 2
    kolor = vbRed
    Case 3
    kolor = vbGreen
    Case 4
    kolor = vbYellow
    End Select
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
    .MinPoint.Modify newtype:=xlConditionValueLowestValue
    .MaxPoint.Modify newtype:=xlConditionValueHighestValue
    End With
    With Selection.FormatConditions(1).BarColor
    .Color = kolor
    .TintAndShade = 0
    End With
    Next pt
    Next ws

    exit_ColorPivot:
    Exit Sub

    err_ColorPivot:
    MsgBox "Error: " & Err.Number & "(" & ws.Name & " – " & pt.Name & ")" & vbCrLf & "==============================" & vbCrLf _
    & Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume exit_ColorPivot

    End Sub

  215. pt.PivotItems.DatabodyRange.Select ‘<===== this line is broken, but does work if using pt.DataBodyRange.Select This gives you an error because (1) the hierarchy is pivot table - pivot field - pivot item, and (2) you would need to specify which pivot item using PivotItems(x). To exclude the subtotals, you can assign the DataBodyRange to a range variable, then resize this by one less than the number of rows and/or columns, as appropriate. Use pt.RowGrand and .ColumnGrand to check whether there is a total row or column.

  216. How would you reference a column in a Pivot Table with multiple Value fields? For example, each year had the order amount values you originally gave, and they also had a column of something else by year (maybe a count or something else). I’m trying to reference jsut the count column within a specific year and month. I’ve been using combinations of Intersect to no avail.

    Your table would look like:
    2003 2004 2005
    Order Amount Count Order Amount Count Order Amount Count
    Jan
    Feb
    Mar
    Apr

  217. Intersect(pt.PivotFields("Years").PivotItems("2004").DataRange, _
        pt.PivotFields("Sum of Count").DataRange).Select
  218. How would you select the Grand Totals. I would like to bold them

  219. Hi everyone
    Firts at all, my congrats for very useful post.
    Question
    May I use pivot to:
    Merge, Sort, and delete dupicate IP ranges.?
    I’am working in ipfilter tool project, and think perhaps pivot tech can do easier my job.
    Thank in advance.

  220. The difficulty is that pivot tables can group or sort by numerical values, but IP addresses are strings comprised of four numbers delimited by periods. You could probably split the IP addresses, pivot on these, and then manipulate the resulting output to generate the affected IP addresses.

  221. Thank you so much for this useful tutorial. It has helped me so much. I thought of an alternative method to find the grand total of a pivot table:

    For Each pvItem In pt.DataBodyRange.Cells
    grand_total = pvItem
    Next pvItem

    MsgBox grand_total

    The ‘For Each’ loop will go through each cell, and the last cell saved will be the grand total. I don’t think this is a repost, rather it is a bit derivative of some earlier posts. I hope this helps.

  222. Aren –
    Unique approach. This gives the value of the last cell. One comment: don’t use “pvtItem” as your loop variable, since its name implies it is a pivot item, and it’s a cell. Declare the variable as a range and use an appropriate name, like rPTCell.
    A similar approach to find the grand total cell (not just value), but without looping, is:

    Dim rGrandTotal As Range
    With pt.TableRange1
        Set rGrandTotal = .Cells(.Cells.Count)
    End With

    Of course, this only gives you the bottom right cell, whether or not the pivot table has row and column totals.

  223. Thanks for the reply.
    You’re right, I used split in the app I developed with VS2008, and it works well. Only looking for other ways to approach the same problem, (sort IP addresses).
    You imagine the following scenario.

    The app downloads blacklists which contain IP ranges in a text file, from different sources.
    These blacklists are classified by filtering levels.
    All these lists should to be merge in a text file called ipfilter.dat

    Sort ips ranges.
    If an new IP address is already contained by an IP range in the ipfilter file, this IP is to be removed, if not, whether there is a range that may contain such IP, merge it to this range. Example:

    IP Range alredy exinting in ipfilter file.
    1.255.255.8 – 1.255.255.20

    New IP address blacklisted, for to be added to the ipfilter file.
    1.255.255.7

    result
    1.255.255.7 – 1.255.255.20

    The adjusted ipfilter file is saved in another application that used it to filter these ip, obeying the filter level for each one.
    I must add that these ips are ranges that for one reason or another, we should not connect with them.
    The app will not connect to any IP in this range.(refer to example)
    At the moment only one access levels are implemented. A value below 127 means that any connection-attempt is denied.

    If anyone is interested in this topic, here is an interesting link.

    Greetings.

  224. Jon, this tutorial is awesome. Thank you very much.Just one question. If I want all the hours and costs of the below section, how would I write it? The closest I can think of is this, but it doesn’t work.

    Screenshot: http://i.imgur.com/tBlbnfn.jpg

    pt = sh1.PivotTables(“PivotTable1”)
    sh1 = ActiveWorkbook.Sheets(“Sheet1”)

    Intersect(pt.PivotFields(“Project Name”).PivotItems(“Project 1”).DataRange.EntireRow, _
    pt.PivotFields(“Activity Name”).PivotItems(“Activity Name 1”).DataRange.EntireRow, _
    pt.PivotFields(“Resource Name”).PivotItems(“Resource Name 1”).DataRange.EntireRow, _
    pt.PivotFields(“Activity Code”).PivotItems(“1”).DataRange)

  225. Actually, Jon. It did pretty much work. I just had to set that intersect to a range, and then use copy destination:= to paste it into another range. Cheers, you can delete the previous post.

  226. Excellent article. Very useful. Thank you.

  227. Hi Jon –

    Need your help, i am try to do the conditional formatting in Pivot for my below data. I need to color code in
    my databodyrange if it is “Good” then it should be Green and if it is “Bad” it should be Red. Please help me with code..

    Appreciate your help.. please
    Sum of Calls Year WK Booking
    2013
    26 32
    Seller Buyer Good Bad Good Bad
    INDIA US 4 7 1
    UK 5 1 6
    AUS 4 4 1
    FRA 5 2 5 1
    GRE 4 2
    CAN 6 1 5 1
    INDIA Total 28 4 29 4
    Grand Total 28 4 29 4

  228. This is perhaps one of the most useful web pages for VBA…ever

  229. Hello Jon,

    I didn’t find your web site while I was learning to handle pivot tables in VBA, but I sure would have learned faster !

    I have now managed to do everything I want with them, but I still have one issue :
    Working in an international company, people from other countries will use the macro I wrote.
    I discovered than when you group by months and years a date column in the pivot table, you don’t get the same result if you are in France, USA or Sweden : the spelling of the months will differ as following :

    France – USA – Sweden
    Jan – Jan – Jan
    Fév – Feb – Feb
    Mai – May – Maj
    Mars – Mar – Mar
    …..

    So when I want to get the data from every month with a loop like this :
    Pivot1.GetPivotData(“Count of Claim “, _
    “Years”, y, _
    “Vehicle Assembly Date “, Tabl_Month(i))

    I need to create a Tabl_Month() like this :
    Tabl_Mois = VBA.Array(“jan”, _
    “feb”, _
    “…”, _
    “dec”)

    But as the spelling of the months will change with the location of the computer, the user needs to change the Tabl_Month() and write it with his language’s months -> it’s not convenient as users don’t know VBA.
    We can also change the location of the computer in the control panel, but it is not very convenient…

    Do you know if we can, using a macro, ask excel to create the pivot in a specified language ?

    Thanks a lot,
    Best regards,

    Syl

  230. Syl –
    The procedure below extracts the months from the “Date” field in my pivot table.

    Sub GetMonths()
      Dim pt As PivotTable
      Dim pf As PivotField
      Dim pi As PivotItem
      Dim sCaption As String
      Dim sList() As String
      Dim iList As Long
      
      ReDim sList(1 To 1)
      iList = 0
      
      Set pt = ActiveSheet.PivotTables(1)
      Set pf = pt.RowFields(1) ' row field 1: closest to data (rightmost)
      For Each pi In pf.PivotItems
        sCaption = pi.Caption
        If Left$(sCaption, 1) <> "< " And Left$(sCaption, 1) <> ">" Then
          iList = iList + 1
          ReDim Preserve sList(1 To iList)
          sList(iList) = sCaption
        End If
      Next
      
      ' sList contains a list of months
      ' even months that aren't visible in pivot table
    End Sub

    Presumably this will return the month abbreviations for whatever language you’re using.

  231. Hello Jon,

    Thanks for your quick answer !

    Your procedure works well, but unfortunatly the output list is always the english months, even if in the pivot table the months are written in french :/

    I tried changing ‘sCaption = pi.Caption’ by ‘sCaption = pi.Value’ or ‘sCaption = pi.Text’.
    For the .value it works but still gives me the english months, and .text is not a valid property…
    Any idea how to solve this ? :s

    I really think this is the way to do what I want and I will dig further, thanks a lot !

    Syl

  232. Yeah OK I figured out how to do it !

    I will use a command like this one :
    pt.PivotFields(“Years”).PivotItems(year).LabelRange.Select
    with a loop over ‘year’ to select any year that appear, I don’t care which one.

    And then I will use something like :

    ReDim Preserve Table_Months(11)
    For i = 1 To 12
    Table_Months(i – 1) = Cells(Selection.Row + i, Selection.Column).Value
    Next i

    And so it gives me the months as they are written in the excel sheet.

    Again, thank you for your disponibility and your advices, you’re making the world a better place ! ;)

    Have a nice day,
    Syl.

  233. Syl –
    Glad you’ve figured it out. VBA keeps two sets of formulas, one in the local language and one in US English. It must also somehow keep two sets of the month abbreviations. Your trick made VBA find the actual values in the cells (the local values), instead of the internal US English values my approach provided.

  234. Hi peter,

    I’m trying to create an automated function that creates a separate sheet for a pivot grand total. My page has five separate pivot tables on the one page, each requiring a page created separately. The double click that pulls all the information into one page is the manual version. How do I name the separate cells and individual tables? As these cell references often change due to changing data I’m frankly flummoxed.

  235. Hi peter,

    I’m trying to create an automated function that creates a separate sheet for a pivot grand total. My page has five separate pivot tables on the one page, each requiring a page created separately. The double click that pulls all the information into one page is the manual version. How do I name the separate cells and individual tables? As these cell references often change due to changing data I’m frankly flummoxed.

    Regards

    David

  236. Hello Jon,
    Thanks for your guide – it’s really useful. How can I reference the value (i.e. PivotItem) in a PivotField? Using your example above, the Country field in the report filter is set to the UK – how do I reference the ‘UK’ value? I obviously don’t want to specify the UK, but rather whatever value is selected. I therefore can’t have a static value of ‘UK’, but need something dynamic that would work regardless of any changes to the PivotField selection.
    Thanks!

  237. Thank you for this comprehensive set of examples. One thing to try – because I believe we have a bizarre Excel bug here: ( using Excel 2010)

    create a pivot table with 2 data fields.
    place the data fields in Column alignment
    Create a single row field.
    Make sure there is no other column field, other than the Data Fields.

    Now you’ll find that all the pivotitem ranges relating to the Row pivot field are one row too low.

    Now add another column field over the data fields.
    It all works again as expected.

    Can anyone reproduce?

  238. Hi
    Great tutorial :-)))

    I’ve recently changed from Excel 2003 to 2007. Some of my VB code no longer works…
    I harvest data on a monthly basis into Excel. Each new page is named with the import date, and the import date is also stored in Range “O2” on each monthly import page.
    My Pivot collects data from 4 different pages, and they are renamed to ‘Rådata-IKT’, ‘EnRapp’, ‘ToRapp’ and ‘TreRapp’ in order to be able to collect the data. All other pages are still named with their import date.
    My Excel 2003 code then made a refresh of the pivot, and renamed the PivotFields(1).DataRange to the import date from Range “O2” from the 4 different pages

    The code no longer rename the PivotFields(1).DataRange, but refreshes the DataBodyRange
    2003 code that worked brilliantly:
    ActiveSheet.PivotTables(1).TableRange1.Select
    ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=Array _
    (Array(“‘Rådata-IKT’!R1C1:R850C14”, Sheets(“Rådata-IKT”).Range(“O2”)), _
    Array(“‘EnRapp’!R1C1:R850C14”, Sheets(“EnRapp”).Range(“O2”)), _
    Array(“‘ToRapp’!R1C1:R850C14”, Sheets(“ToRapp”).Range(“O2”)), _
    Array(“‘TreRapp’!R1C1:R850C14”, Sheets(“TreRapp”).Range(“O2”)))

    Also tried only changing the name without collecting the import date from “O2”
    ActiveSheet.PivotTables(1).TableRange1.Select
    ActiveSheet.PivotTableWizard SourceType:=xlConsolidation, SourceData:=Array _
    (Array(“‘Rådata-IKT’!R1C1:R800C14”, “raadata”), Array(“EnRapp!R1C1:R800C14”, _
    “enrapp”), Array(“ToRapp!R1C1:R800C14”, “torapp”), Array( _
    “TreRapp!R1C1:R800C14”, “trerapp”))

    The code
    ActiveSheet.PivotTables(1).PivotFields(1).PivotItems(“Old-name”).Caption = “new-name”
    does not work as the ‘old-name’ constantly changes because I rename the pages as described earlier.

    I’ve tried everything… and are desperately seeking your help
    Morten-from-Norway

  239. Mohammed Faheem says:

    Hi All,

    Anybody, please help me out. I wanted to copy paste the pivot data by automatically selecting the pivot items one by one whatever the list of pivot items available in pivot field. The pivot items can be anything numeric or alpha or alphanumeric.My pivot items in pivot field are also not being same every time it keeps on changing.

    Hope I clarified my questions.

    Please provide me the code for the above

  240. Hi
    If I you could get some help with below question I would appreciate it.

    I have a flat pivottable (no DataLabelRange) and nothing in my filter.
    The pivottable has region and country in my RowField and 3 values (AmountUSD, AmountEUR and Hours).

    Now when I use “pt.PivotFields(“Region”).PivotItems(“APJ”).DataRange.Select”, my range is offset by 1 row.
    Why is this?
    And is it save to simply due an offset(-1,0) on this range? Considering multiple users with different systems, but who will not change, only update, the pivottable.

    BR Jonas

  241. This post is quite old now, but I hope you will see my question. I’m really stumped!!
    The article above is fabulous and has helped me many times. What I am struggling with is how to select the DataRange when there are multiple row labels. I want all of the DataRange for all of the RowLabels but without the Labels themselves.
    Much like in your example above, where you selected the ‘Years’ DataRange. I did get different results as the image shown though. To select the ‘Years’ as in the pic, I changed this:
    pt.PivotFields(“Years”).LabelRange.Select
    to this:
    pt.PivotFields(“Years”).DataRange.Select
    I’ll try to post a pic.

    I would like to select both the ‘Years’ and ‘Order Date’ DataRanges. No amount of searching or trial and error has been successful for me. Any suggestions? Thanks!!
    Thanks!
    Tanya

  242. Tanya –

    Try this:

    Union(pt.PivotFields("Years").DataRange, pt.PivotFields("Order Date").DataRange).Select

  243. THANK YOU!!!
    That worked beautifully!
    Also, thank you for such a quick response :)

  244. I am trying to write a code that would find the PivotTable associated with the source data. I am stuck up at a very early stage! Any help would be greatly appreciated.

    Sub FindPivotTbl()
    Dim Pt As PivotTable
    Dim Sh As Worksheet
    ‘Debug.Print ActiveCell.Address
    For Each Sh In Worksheets
    For Each Pt In Sh.PivotTables
    If Not Intersect(ActiveCell, Range(Pt.SourceData)) Is Nothing Then

    ‘the code

    End If
    Next
    Next
    End Sub

  245. Is there a way using VBA to go to the relevant cell (i.e. trace precedents) though the GetPivotData formula?

  246. Suril –

    Your code may break down because PT.SourceData is returned as a string, as the address in R1C1 notation. You would need to use Application.ConvertFormula(PT.SourceData, xlR1C1, xlA1) to get the address that Range() will find palatable.

    I can’t imagine a more direct way to trace the precedents of a pivot table value. Of course, if you double click on a pivot table cell, a new sheet is inserted that lists all the records of the source data that the cell is based on.

  247. Hi Jon & everyone else,
    I’ve come across a strange difference between PivotFilters and CurrentPage.
    I’m using an ActiveX calendar control embedded in a user form to pick specific dates to use as filters/pageby values for a number of pivot tables.
    The difference I am seeing is that PivotFilters will accept the date as string in format dd/mm/yyyy but CurrentPage will only accept the date as string in format mm/dd/yyyy.
    I’m also finding that pivots where I have changed the CurrentPage value are not updating.

    Has anyone else experienced this? Any ideas on the CurrentPage format difference or the pivot(s) not refreshing?

  248. Justin –

    What are your regional settings for date format? And by “accept” a date, do you mean “display” a date, or is this something about how you are controlling the filters? I don’t know why the Filter (Page) fields would treat dates differently than Row or Column fields.

    I usually avoid ActiveX calendar controls because of reliability issues (ActiveX issues, plus not guaranteed to be installed on every computer). Is there a reason not to use the filters on the pivot fields, other than keeping multiple pivot tables in sync?

    Have you tried using a Slicer or Timeline? These are relatively new, Slicers introduced in Excel 2010 and Timelines in 2013 (I think). Either let you filter like fields in multiple pivot tables that use the same pivot cache, so you can sync them without the need for code.

  249. I’ve been doing some work on identifying subtotal ranges – including custom subtotals – using the Range.PivotCellType property. Here’s the latest post, which refers back to a previous one: http://yoursumbuddy.com/locating-pivotitem-subtotals/

  250. Thanks, Doug. Good stuff.

  251. Ricardo Madaleno says:

    Hi John,

    Great coding :) thank you very much.

    I was wondering; is it possible to reference the pivotfields through their sourcename instead of the name?
    Their name can be changed and sometimes you might not know the correct name, but the sourcename is fixed so it would be safer to use…

    I know you could loop through all the pivotfields and check their sourcename agains a variable, but if there are too many pivotfields it can slow down your code considerably…

    Thanks

  252. Excellent tips. Thanks.
    however, i’m having trouble in my pivot table with code to highlight header and rows data below itself with variable number of rows.
    Kindly advise.

  253. Arshad –

    Do you mean you want to highlight data outside of the pivot table? You can use TableRange1 or TableRange2 to find the extent of the pivot table, then either count N rows below that, or look for the next row(s) below that with data.

    Do you want to select a range with multiple areas? Find your ranges, called Range1 and Range2, which can be inside or outside the pivot table, then use Union(Range1, Range2) to reference the combined range.

Trackbacks

  1. […] In terms of identifying PivotFields, DataFields and other pivot table ranges in code, Jon Peltier wrote a superb post in 2009 that’s still generating […]

  2. […] Strangely enough, I found some info on this problem at one of my most revisited blogposts that I had somehow missed: Jon Peltier’s Referencing Pivot Table Ranges in VBA […]

  3. […] 34 – Referencing Pivot Table Ranges in VBA […]

  4. […] I'm not sure what part of the pivot table you're copying – this link will tell you how to reference different parts. Referencing Pivot Table Ranges in VBA – Peltier Tech Blog […]

  5. […] Have a look at this site to see how to reference different parts of the pivot table: Referencing Pivot Table Ranges in VBA – Peltier Tech Blog […]

  6. […] Read more on various ranges within a pivot table and their special VBA range names on Jon Peltier’s site […]

  7. […] Check out Jon Peltier's blog Referencing Pivot Table Ranges in VBA. Might be helpful. Cheers, […]

  8. […] different if you had grandtotals Here's some examples of referencing different pivot ranges: Referencing Pivot Table Ranges in VBA – Peltier Tech Blog Referencing an Excel Pivot Table Range using […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0