anglophobe
New Member
- Joined
- Apr 15, 2008
- Messages
- 4
I've set up some code to autogenerate a Pivot Table from an array of data, and I've set it to have Print Titles set to True, so if the pivot table spans multiple printed page, we can see the row info.
I've set it at the bottom of the creation code, and also tried setting it when it is printed, and it doesn't take the value.
If I right click on the pivot table and go to table options, then click ok (without touching anything else) it then uses Print Titles fine.
Any suggestions?
I've set it at the bottom of the creation code, and also tried setting it when it is printed, and it doesn't take the value.
If I right click on the pivot table and go to table options, then click ok (without touching anything else) it then uses Print Titles fine.
Any suggestions?
Code:
Sub CreatePivot(reportName, wsName, i, aReportList)
'Creates Pivot Table based upon columns listed in Report Config and then formats the pivot table
Sheets("Data " & wsName(i)).Select
'Create Pivot
If Worksheets("Data " & wsName(i)).UsedRange.Rows.Count > 1 Then
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion.Address).CreatePivotTable _
TableDestination:="'" & wsName(i) & "'!R3C1", TableName:="Pivot" & wsName(i), DefaultVersion:=xlPivotTableVersion10
For j = 1 To UBound(aReportList, 1)
If aReportList(j, 1) = reportName Then
If aReportList(j, 2) = wsName(i) Then
If aReportList(j, 4) = "Row" Then
With Sheets(wsName(i)).PivotTables("Pivot" & wsName(i)).PivotFields(aReportList(j, 3))
.Orientation = xlRowField
.Position = aReportList(j, 5)
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
ElseIf aReportList(j, 4) = "Column" Then
With Sheets(wsName(i)).PivotTables("Pivot" & wsName(i)).PivotFields(aReportList(j, 3))
.Orientation = xlColumnField
.Position = aReportList(j, 5)
End With
ElseIf aReportList(j, 4) = "Data" Then
Sheets(wsName(i)).Activate
Sheets(wsName(i)).PivotTables("Pivot" & wsName(i)).AddDataField _
ActiveSheet.PivotTables("Pivot" & wsName(i)).PivotFields(aReportList(j, 3)), _
Left(aReportList(j, 3), Len(aReportList(j, 3)) - 1), xlSum
End If
End If
End If
Next j
With ActiveSheet.PivotTables("Pivot" & wsName(i))
.PrintTitles = True
End With
End If