Pivot Table Formatting.

[ianb]

Hi,


I have pivot tables created in 2003 and when I am using 2007 i can not change the design styles as I have been using colours on the background and forgound which make the design change, change only parts of the pivot table. text will disapear, etc


How can I reset the pivot tables to make the pivot tables work in design mode for excel 2007. I think I made the background white only hence it will not allow the changes.


Many Thanks. this is an amazing place to find answers..... without this web site i would not of completed by work...

 

[ianb]

VBA Program to reset pivot tables back to default settings and then to select the design for all pivot tables. now that would be perfect....

 

[ianb]

I already have this part of my program which removes old data field if any one needs this :


Sub DeleteOldPivotData()

'prevents unused items in non-OLAP PivotTables

Dim pt As PivotTable

Dim ws As Worksheet

Dim pc As PivotCache


'change the settings

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next pt

Next ws


'refresh all the pivot caches

For Each pc In ActiveWorkbook.PivotCaches

On Error Resume Next

pc.Refresh

Next pc


End Sub

 

[ianb]

Also I think it may contain some of these :


With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

Selection.Locked = False

Selection.FormulaHidden = False

 

[ianb]

PIVOT TABLES

HIGHLIGHT ALL FORMAT CELLS

BACKGROUND COLOUR – NO FILL.


This gives me the formatting I need. I am after a program that will setting all pivot tables to the same design

 

[ianb]

Sub PivotTableConfig()


Dim pt As PivotTable

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables


***********pt = "PivotStyleDark2"***********


Next pt

Next ws


End Sub

 

[ianb]

Here it is my last attempt !!! 'pt = "PivotStyleDark2" (How do I do this with the settings.)


Sub PivotTableConfig()


Dim pt As PivotTable

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables


'change the settings

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

'pt = "PivotStyleDark2" (How do I do this with the settings.)

Next pt

Next ws


Next pt

Next ws


End Sub


'change the settings

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next pt

Next ws

 

[ianb]

HI ALL I THINK THIS IS IT... Any one is welcome to add to it if they have any ideas.


Sub PivotTableConfig()


Dim pt As PivotTable

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables


'*****************************


With Selection.Interior


.Pattern = xlNone

.PatternColorIndex = xlAutomatic

.TintAndShade = 0

.PatternTintAndShade = 0


End With


'Change Name of style if required.


pt.TableStyle2 = "PivotStyleDark5"


Selection.Locked = False

Selection.FormulaHidden = False


pt.ShowTableStyleRowStripes = True

pt.ShowTableStyleColumnStripes = True


'*****************************


Next pt

Next ws


End Sub

 

[SirJB7]

Hi, ianb!


This topic is the same as last question of this, of same day:

http://chandoo.org/forums/topic/pivot-tables-2003-to-2007


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!


EDIT: BTW, do you know that you can edit your own posts within a certain time period? Doing this maybe it reduces the number of subsequent posts from the same author in a topic. You have 8! upwards which might have been reduced to 2.