expanding a macro to do more

  • Thread starter Thread starter jcontrer
  • Start date Start date
J

jcontrer

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:=xlDatabase, 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 Where:=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.
 
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.
 
Back
Top