Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Pivot Table by Gran total automaticaly
How do I sort my pivot table by the grand total...? I can do it manualy but
if I change the criteria I loose the sorting and have to select all the data and manually sort it again. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Pivot Table by Gran total automaticaly
If you can do it manually, just turn on the macro recorder before running
through the steps. When you are done with the sort, turn off the macro recorder and view the code by hitting Alt+F11 on your keyboard. Also, compare your recorder code to this code: Sheets("Sheet1").Select Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 Pay attention to this part: Sheets("Sheet1").Range("A1").CurrentRegion). You will need to replace your recorded code with that snippet of code (change the sheet name so it matches yours). That will give you a dynamic range; you always capture all the data for your Pivot Table. Otherwise, you will have a fixed (or absolute range) and if you have more data than what is in the recorded macro, you will miss what is at the bottom; quite simply your Pivot Table will display incorrect results. Give it a go and let me know how it works out for you!! HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Alberto Ast" wrote: How do I sort my pivot table by the grand total...? I can do it manualy but if I change the criteria I loose the sorting and have to select all the data and manually sort it again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Pivot Table by Gran total automaticaly
This is what I get from my macro
Range("A6:D189").Select Range("D6").Activate Selection.Sort Key1:="R6C4", Order1:=xlDescending, Type:=xlSortValues, _ OrderCustom:=1, Orientation:=xlTopToBottom Range("E1").Select Would you gave me the complete set up... I have not much experience using the macros on the view code where I think is where it should go. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
sort by grand total in pivot table | Excel Programming | |||
How do I sort a Piviot table by the Total field in Excel 2002 | Charts and Charting in Excel | |||
Showing Pivot Table total on Associated Pivot Chart | Excel Discussion (Misc queries) | |||
% of Running Total to Grand Total in Pivot Table | Excel Programming |