Microsoft Office Tutorials and References
In Depth Information
Creating Multiple Pivot Tables
CurrentRegion)
Row = 1
For i = 1 To 14
For Col = 1 To 6 Step 5 ‘2 columns
ItemName = Sheets(“SurveyData”).Cells(1, i + 2)
With Cells(Row, Col)
.Value = ItemName
.Font.Size = 16
End With
‘ Create pivot table
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=SummarySheet.Cells(Row + 1, Col))
‘ Add the fields
If Col = 1 Then ‘Frequency tables
With PT.PivotFields(ItemName)
.Orientation = xlDataField
.Name = “Frequency”
.Function = xlCount
End With
Else ‘ Percent tables
With PT.PivotFields(ItemName)
.Orientation = xlDataField
.Name = “Percent”
.Function = xlCount
.Calculation = xlPercentOfColumn
.NumberFormat = “0.0%”
End With
End If
PT.PivotFields(ItemName).Orientation = xlRowField
PT.PivotFields(“Sex”).Orientation = xlColumnField
PT.TableStyle2 = “PivotStyleMedium2”
PT.DisplayFieldCaptions = False
If Col = 6 Then
‘ add data bars to the last column
PT.ColumnGrand = False
PT.DataBodyRange.Columns(3).FormatConditions. _
AddDatabar
With pt.DataBodyRange.Columns(3).FormatConditions(1)
.BarFillType = xlDataBarFillSolid
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
End With
End If
Next Col
Row = Row + 10
 
Search JabSto ::




Custom Search