How do I prevent a PivotChart from becoming a regular chart on sheet copy?

1 . In my humble experience of copying Pivot Chart, I didn't copy the Sheet but the Chart :

Sheets("Graph1").ActiveChart.ChartArea.CopyActiveSheet.PasteSpecial Format:="Objet Dessin Microsoft Office", _    Link:=True, DisplayAsIcon:=False

Have you tried to create an empty page and to paste the Chart in it?You probably will have to change the format which is in French, but that should do the trick!

2 . No clue....

3 . For creating an Pivot Table from scratch, I have no magical tricks, but I use this as a template :

Sub Create_DCT(ByVal Source_Table_Name As String, ByVal DCT_Sheet_Name As String, ByVal DCT_Name As String)    DeleteAndAddSheet DCT_Sheet_Name    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _        SourceData:=Source_Table_Name, _        Version:=xlPivotTableVersion14). _        CreatePivotTable _        TableDestination:=DCT_Sheet_Name & "!R3C1", _        TableName:=DCT_Name, _        DefaultVersion:=xlPivotTableVersion14End SubSub Add_Fields_DCT(ByVal DCT_Sheet_Name As String, ByVal DCT_Name As String)    Dim Ws As Worksheet    Set Ws = Worksheets(DCT_Sheet_Name)    'Organized filters    With Ws.PivotTables(DCT_Name).PivotFields("Cluster")        .Orientation = xlPageField        .Position = 1    End With    With Ws.PivotTables(DCT_Name).PivotFields("Region")        .Orientation = xlPageField        .Position = 2    End With    With Ws.PivotTables(DCT_Name).PivotFields("Account")        .Orientation = xlPageField        .Position = 3    'Organized rows    With Ws.PivotTables(DCT_Name).PivotFields("Family")        .Orientation = xlRowField        .Position = 1    End With    With Ws.PivotTables(DCT_Name).PivotFields("Sub_family")        .Orientation = xlRowField        .Position = 2    End With    With Ws.PivotTables(DCT_Name).PivotFields("Invoice_Country")        .Orientation = xlRowField        .Position = 3    End With    With Ws.PivotTables(DCT_Name).PivotFields("Product")        .Orientation = xlRowField        .Position = 4    End With    'Columns : none'    With Ws.PivotTables(DCT_Name).PivotFields("Family")'        .Orientation = xlColumnField'        .Position = 1'    End With'Data fields (adding, modifying, formatting and compacting)    'Data fiels : Adding    'With Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name)        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Quantity"), "Total Qty", xlSum        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Quantity"), "Avg Qty", xlAverage        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Quantity"), "Qty of Orders", xlCount        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("TotalAmountEUR"), "TO (€)", xlSum        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("UPL"), "Avg UPL", xlAverage        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Discount"), "Avg Discount", xlAverage        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Discount"), "Min Discount", xlMin        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("Discount"), "Max Discount", xlMax        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("PVU"), "Min PVU", xlMin        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("PVU"), "Max PVU", xlMax        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("(PVU-PRI)/PVU"), "Gross Margin", xlAverage        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("(PVU-TC)/PVU"), "Net Margin", xlAverage        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("PVU-PRI"), "Gross Profit (€)", xlSum        Ws.PivotTables(DCT_Name).AddDataField Ws.PivotTables(DCT_Name).PivotFields("PVU-TC"), "Net Profit (€)", xlSum    'End With    'Data fiels : Modifying'    With Ws.PivotTables(DCT_Name).PivotFields("Somme de Quantity")'        .Caption = "Moyenne de Quantity"'        .Function = xlAverage'    End With    'Data formatting    With ActiveSheet.PivotTables(DCT_Name)        .PivotFields("Total Qty").NumberFormat = "# ##0"        .PivotFields("Avg Qty").NumberFormat = "# ##0,#"        .PivotFields("Qty of Orders").NumberFormat = "# ##0"        .PivotFields("TO (€)").NumberFormat = "# ##0 €"        .PivotFields("Avg UPL").NumberFormat = "# ##0 €"        .PivotFields("Avg Discount").NumberFormat = "0,0%"        .PivotFields("Min Discount").NumberFormat = "0,0%"        .PivotFields("Max Discount").NumberFormat = "0,0%"        .PivotFields("Min PVU").NumberFormat = "# ##0 €"        .PivotFields("Max PVU").NumberFormat = "# ##0 €"        .PivotFields("Gross Margin").NumberFormat = "0,0%"        .PivotFields("Net Margin").NumberFormat = "0,0%"        .PivotFields("Gross Profit (€)").NumberFormat = "# ##0 €"        .PivotFields("Net Profit (€)").NumberFormat = "# ##0 €"    End With'Compact row fields to minimumFor Each PivIt In ActiveSheet.PivotTables(DCT_Name).PivotFields("Sub_family").PivotItems    PivIt.DrillTo "Invoice_Country"Next PivItFor Each PivIt In ActiveSheet.PivotTables(DCT_Name).PivotFields("Family").PivotItems    PivIt.DrillTo "Sub_family"Next PivItFor Each PivIt In ActiveSheet.PivotTables(DCT_Name).PivotFields("Family").PivotItems    PivIt.DrillTo "Family"Next PivItEnd Sub

And my custom fucntion DeleteAndAddSheet :

Public Function DeleteAndAddSheet(ByVal SheetName As String) As WorksheetFor Each aShe In Sheets    If aShe.Name <> SheetName Then    Else        Application.DisplayAlerts = False        aShe.Delete        Application.DisplayAlerts = True        Exit For    End IfNext aSheSheets.Add After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = SheetNameSet DeleteAndAddSheet = ThisWorkbook.Worksheets(Worksheets.Count)End Function

Hope it will help you!

i suggest creating a second workbook linked to the source data sheet, then create matching pivot tables in the second sheet (both are essentially using the same data to populate) -- I'm not sure on the next point, does the client need to have a linked version of the charts or is this purely for auto reporting?

If it is for auto reporting then I'd suggest a new approach entirely, using the current workbook you have - create a macro to run when required ontime, daily/weekly/monthly etc and send the charts (from the sheets you select only) as a pdf - i have some sample code for this if required :)

I resolved the issue by using the .XLTM macro-enabled template as an actual template!

Instead of opening the template file then copying worksheets that are needed from the template to a new workbook, I now open the .XLTM, remove the sheets that aren't needed for a particular client's report. This has completely eliminated the need to copy sheets, charts and graphs, and removed all the errors created by attempting to do so.

This doesn't specifically resolve the issue of how to copy the pivot chart without losing its pivot chartiness, but it resolved the bigger picture issue of how do I make this happen (I did say I was open to alternate suggestions).