changing a macro to do pivot tables

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top