Hi,

I'm struggling to copy a pivot table to a new workbook and worksheet. The code below creates the pivot table;

Sub Macro3()
'
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DATA!A1:R50000").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Proj")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Activity")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Week Comm")
        .Orientation = xlColumnField
        .Position = 1
    End With
    'With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
    '    .Orientation = xlColumnField
    '    .Position = 1
    'End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Quantity"), "Sum of Hours", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amount"), "Sum of Mat Costs", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.Name = "pivot data sheet"
End Sub
but this code is failing once it gets past the creation of new workbook/worsheet stage;

Sub CreateBlankWorkbook()

Dim PT As PivotTable
Dim PTCache As PivotCache
Dim WSR As Worksheet
Dim WBN As Workbook

Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = "Report"
With WSR.[A1]
    .Value = "New Report"
    .Font.Size = 20
End With

ActiveSheet.PivotTables("PivotTable2").Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'PT.TableRange2.Clear
Set PTCache = Nothing

End Sub
Can anyone help please?

Thanks,

Louise