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

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