Results 1 to 3 of 3
2009-03-31, 07:17 #1
- 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.
2009-03-31, 08:33 #2
- Join Date
- Nov 2002
- New York, New York, USA
- Thanked 19 Times in 19 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.
' Macro will Format Cols Headings for Pivot Table
.WrapText = True
.Orientation = 90
2009-03-31, 08:48 #3
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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!)