Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing a macro to do pivot tables
I have a sheet in my workbook labeled "data" which contains all my data. I've
recorded a macro that creates a pivot table and a chart in separate sheets from the data in A1:T232 of "data". it gives me the data that i need for Q1 (one of the questions in a survey). the macro looks like this: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "DATA!R1C1:R232C20").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Charts.Add ActiveChart.Location Whe=xlLocationAsNewSheet ActiveChart.PlotArea.Select ActiveChart.ChartType = xl3DPieExploded ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _ PivotTable.PivotFields("Q 1"), "Count of Q 1", xlCount With ActiveChart.PivotLayout.PivotTable.PivotFields("Q 1") .Orientation = xlRowField .Position = 1 End With ActiveChart.SeriesCollection(1).Select ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _ False, HasLeaderLines:=True, ShowSeriesName:=True, ShowCategoryName:= _ False, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False With ActiveChart.ChartGroups(1) .VaryByCategories = True .FirstSliceAngle = 0 End With ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _ False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _ True, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False Sheets("Chart1").Select ActiveChart.SeriesCollection(1).DataLabels.Select ActiveChart.PlotArea.Select ActiveChart.HasLegend = True ActiveChart.Legend.Select Selection.Position = xlBottom ActiveChart.SeriesCollection(1).Select With ActiveChart.ChartGroups(1) .VaryByCategories = True .FirstSliceAngle = 20 End With ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _ False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _ True, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False End Sub what i need is to have this macro create the charts and tables for Q1 through Q16 (different questions in a survey) in the same way that this macro creates just Q1. I have no idea what to change or even whether it can be done. your assistance would be appreciated. -- thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables & changing data | Charts and Charting in Excel | |||
How do I keep my pivot tables format from changing? | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel | |||
Dynamically changing several pivot tables at once | Excel Discussion (Misc queries) | |||
changing column order in pivot tables? | Excel Programming |