Halldo
01-29-2007, 12:13 PM
Hello all,
I have a weekly report that is used globally that also maintains history in a pivot table. Out of this pivot table I have attempted to grab the first value in the pivot table to return it to how it was in the beginning. At first I would use the text "(ALL)" which works fine in the US and Canada for the most part. But when working with France and Germany I found that I recieved an Error due to the language, (All) is (Tous) in French and (Alle) in Germany. So I developed the method below to capture the first value when the tool starts, however because the tool is user activated it can be changed to a different selection prior to starting. This means that (All), regardless of language may not be selected. This is the real question, is there a method to allow the pivot table to return to it default value regardless of language. Any help would be greatly appreciated, as I am truely :banghead: !!!!
Sub TestPivot()
Dim PT As PivotTable, tmpitem As PivotItem, Hold As String
Set PT = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable1") 'Identify location of Pivot to work with
'Hold = "(All)" Old Method
Hold = Range("B1") 'New Method - grab first value
For Each tmpitem In PT.PageFields("ESL").PivotItems 'Toogle through all pivot fields
Range("B1") = tmpitem.Value 'Update table for value
'Code omitted to gather data
Next tmpitem 'Repeat cycle for all items in list
Range("B1") = Hold 'return pivot to first value
End Sub
I have a weekly report that is used globally that also maintains history in a pivot table. Out of this pivot table I have attempted to grab the first value in the pivot table to return it to how it was in the beginning. At first I would use the text "(ALL)" which works fine in the US and Canada for the most part. But when working with France and Germany I found that I recieved an Error due to the language, (All) is (Tous) in French and (Alle) in Germany. So I developed the method below to capture the first value when the tool starts, however because the tool is user activated it can be changed to a different selection prior to starting. This means that (All), regardless of language may not be selected. This is the real question, is there a method to allow the pivot table to return to it default value regardless of language. Any help would be greatly appreciated, as I am truely :banghead: !!!!
Sub TestPivot()
Dim PT As PivotTable, tmpitem As PivotItem, Hold As String
Set PT = ThisWorkbook.Worksheets("Summary").PivotTables("PivotTable1") 'Identify location of Pivot to work with
'Hold = "(All)" Old Method
Hold = Range("B1") 'New Method - grab first value
For Each tmpitem In PT.PageFields("ESL").PivotItems 'Toogle through all pivot fields
Range("B1") = tmpitem.Value 'Update table for value
'Code omitted to gather data
Next tmpitem 'Repeat cycle for all items in list
Range("B1") = Hold 'return pivot to first value
End Sub