Pivot Table Macro Problems

[timbo1957]

Hi,

This is a macro I recorded using the recorder.

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'HOME D&C'!R1C4:R639C21").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable7", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable7").AddFields RowFields:=Array("FL/BL", _
        "COMMUNITY", "ISBN", "Title", "Data")
    With ActiveSheet.PivotTables("PivotTable7").PivotFields("Net Qty")
        .Orientation = xlDataField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable7").PivotFields("Net Val").Orientation = _
        xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Range("C3").Select
    ActiveSheet.PivotTables("PivotTable7").PivotFields("ISBN").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Range("E3").Select
    With ActiveSheet.PivotTables("PivotTable7").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

This is a macro that a member gave me yesterday for a different problem which I have tried in vain to adapt for the above.

    Dim Lr1 As Long
        
    Dim PT  As PivotTable

    Lr1 = Range("B" & Rows.Count).End(xlUp).Row

    Set PT = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                "'EXPORT OTHER IMPRINTS'!R1C4:R" & Lr1 & "C18").CreatePivotTable(TableDestination:=Sheets.Add.Range("A1"), _
                DefaultVersion:=xlPivotTableVersion10)
    
    With PT
        .AddFields RowFields:=Array("FL/BL", "COMMUNITY", "ISBN", "Title", "Data")
        With .PivotFields("Net Qty")
            .Orientation = xlDataField
            .Position = 1
        End With
        With .PivotFields("Net Val")
            .Orientation = xlDataField
            .Position = 2
        End With
    End With

It works so far and then stops. If anyone could help I would be very grateful. I have attached a blank workbook with the headings only.