Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Warwick, Warwickshire, England
    Thanked 0 Times in 0 Posts
    I have a pivot table which has course names across the top and names down the size with date of course completeion as the data. All works fine with that.

    The course names were long, so I formatted the row that contains them to have text orientation at 90degrees and wrap text turned on. This allowed me to produce a smaller, narrow column. Again this works fine.

    Problem comes when I add courses to the Access database which supplies the data to the pivot table. The cell which the new course name goes into changes to standard text - wide column, and horizontal.

    I tried changing the 'preserve formatting' in the pivot table options, but that had no effect.

    I tried pasting the data into the spreadsheet and linking directly to it rather than linking to it via an Access Query and had the same problem.

    Any thoughts?


  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    New York, New York, USA
    Thanked 20 Times in 20 Posts
    [quote name='jaf90' post='768427' date='31-Mar-2009 13:17']Any thoughts?


    The problem is not with the Pivot Table. It is with the formatting you selected in Excel.
    If each time you receive new data you generate a new Pivot Table and you select the table to either go to a new area of the existing worksheet or to go to a new worksheet you will lose your formatting.

    One possible solution is to use the same Pivot Table and change the PivotTable Data Range to be large enough to accommodate the current data as well as future data.

    Then when new data arrives copy it to the Pivot Table Data Range.
    Go to the existing Pivot Table and hit Refresh. The Table should update and reflect your new data.

    If you desire run a new Pivot Table each time try the below Macro
    Run it after the Pivot Table is Created.

    The below Macro assumes the row to be formatted is Row 4 of the Active Worksheet.
    You can modify the below to adjust to your facts.

    Sub MyCols()
    ' Macro will Format Cols Headings for Pivot Table
    With Selection
    .WrapText = True
    .Orientation = 90
    End With
    End Sub


    Tom Duthie

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    If you want the table to be formatted automatically when it's updated, you can create code similar to that posted by Tom Duthie in the Worksheet_PivotTableUpdate event in the worksheet module.
    See Post 737026 for an example of such code (with different formatting!)

Posting Permissions

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