Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format button to save work (Excel 2000)

    I create a master pivot table for an analysis of a set of General Ledger accounts with a page field for the account number and a first row field (that is the highest, "left-ist" row, correct?) for the branch account subdivision. As I change the page field, the total lines relocate based on the number of branches involved. To ease visual review, I format the Total Line (I only have one for each-I hide the other column B and C row subtotals) as dark grey with black outlines. I am trying to think of a nice little macro that I can attach to a button that will save me from manually finding and re-formatting all the total rows each and every time I change the page field. This is a time-consuming nuisance and a perfect opportunity to get Excel to do the work.
    I would first need to find that right-most boundary column that is always captioned "Sum of Net". I guess I would somehow stash its column count property in some variable. Then I would find any cell in column A that has the word "Total" within it, and format the group of cells in that row, as far right as the boundary cell, in the format I want. Is this the proper way to approach the problem? Can I get a few hints? Is the Pivot table a collection? How would I go about finding those cells within any table that had the word "Total" in them, and how would I specify that cell-and-all-the-rest-over-to "Sum of Net"'s column?
    I would save tons of time just being able to click my macro button do do this job. The Auto Formats I have tried are too much. All I want is to have my Branch Total line with a light grey background. (Could I specify the grey % or am I bound to the Fill Color values in that drop-down box?)
    Thanks in advance for all comments.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format button to save work (Excel 2000)

    Does this get you started?

    <pre>option Explicit
    Sub FormatPivot()
    Dim PT As PivotTable
    Dim wks As Worksheet
    Dim rCell As Range
    Dim rFormat As Range
    Set wks = Worksheets("Sheet1")
    Set PT = wks.PivotTables("myPivotTable")

    Set rFormat = Nothing
    For Each rCell In PT.TableRange1
    If InStr(rCell, "Total") <> 0 Then
    If rFormat Is Nothing Then
    Set rFormat = rCell.EntireRow
    Else
    Set rFormat = Union(rFormat, rCell.EntireRow)
    End If
    End If
    Next
    Intersect(PT.TableRange1, rFormat). _
    Interior.ColorIndex = 15 'light gray
    Set rFormat = Nothing
    Set rCell = Nothing
    Set PT = Nothing
    Set wks = Nothing
    End Sub</pre>


    It takes the pivot table named "myPivotTable" on Sheet1 (change as desired) and looks at all the cells in the "TableRange2" (the pivot range without the page field) and gets the rows of all of the cells that contain "total" and "unions" them to a range.

    After checking all the cells, it "intersects" the rows, with the "pivot table range" and formats the interior color of those cells a light gray (colorIndex = 15, change as desired)

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format button to save work (Excel 2000)

    Thanks! Let me look at this. A student's question: What is the purpose of these Set rFormat=Nothing statements? I would have spent a year trying to figure this out. I will try and implement it and get back. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format button to save work (Excel 2000)

    The first one (middle of code) is to make sure (this is an "anal" one, it should be nothing at this point) that the range is empty.

    The last 4 set xxx = nothing is to ensure that the memories are cleared when the macro is done.

    The code should work with none of them added.

    Steve

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Format button to save work (Excel 2000)

    A different suggestion is to ensure that the totals are always in the same position. We have a similar application, and didn't like our totals and intermediate lines moving around from month to month. Our solution was to add "empty" or zero lines to the bottom of our source data so that the pivot table would always find data for ALL the cells, thus ensuring that the pivot table was always the same shape.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format button to save work-followup

    This is great and works well. What should be done to make it more "generic" in that I'd like to be able to use this on any table regardless of the worksheet name or the Pivot table name. CTTOI, where does one find the pivot table name? It always seems to me to be PivotTable 1 on whatever sheet I use. I'm fishing around the libraries and so on to see if I can figure out what the code is, but that could take forever. Thanks!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format button to save work-followup

    Change to this, if you only have 1 pivot table to work on the active sheet.
    Set wks = ActiveSheet
    Set PT = wks.PivotTables(1)

    This will work on the first pivot of the active sheet no matter the sheet or pivot table name

    You can name the pivot table in the Step 4 of the WIzard (under options)
    Or after it is created, right - click and choose options.

    Both pull the same dialog, it is the top box called "Name"
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •