PrintTitles not being read

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?

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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Upon further experimentation, I can't get print titles to work with other code as well
If I just create a pivot table manually, and then execute the code
Code:
Sub Printitle2()
    
    ActiveSheet.PivotTables("PivotTable1").PrintTitles = True
    
End Sub

On the sheet, It will not put the row titles on each page until I right click on the pivot table and select table options then click ok.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top