Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
expanding a macro to do more
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 -- thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
expanding a macro to do more
Not sure if you solved this already, but can you put the data from each
question on a different worksheet and make a new pivot table and related chart for each one? That is what I have done in the past. Otherwise you will need to change the range you are using for each plot. Hope that helps a little. "jcontrer" wrote: 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 -- thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding Selection | Excel Discussion (Misc queries) | |||
Expanding field | Excel Discussion (Misc queries) | |||
expanding button | New Users to Excel | |||
Expanding data | Excel Worksheet Functions | |||
Help With Expanding A Formula | Excel Worksheet Functions |