Macro Error dataname = ActiveSheet.ListObjects(1).Name

Question

Does anyone see where my error is?

Sub PivotTable()

'

' PivotTable Macro

' PivotTable

'

'

Dim dataname As String

Dim newsheet As String

    dataname = ActiveSheet.ListObjects(1).Name

    Sheets.Add

    newsheet = ActiveSheet.Name

    Rows("15:15").Select

    Range("F15").Activate

    Range(Selection, Selection.End(xlDown)).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        dataname, Version:=xlPivotTableVersion15).CreatePivotTable _

        TableDestination:=newsheet & "!R3C1", TableName:="PivotTable4", DefaultVersion _

        :=xlPivotTableVersion15

    Sheets(newsheet).Select

    Cells(3, 1).Select

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer #")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer Name")

        .Orientation = xlRowField

        .Position = 2

    End With

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("ASR")

        .Orientation = xlRowField

        .Position = 1

    End With

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Original Invoice")

        .Orientation = xlRowField

        .Position = 4

    End With

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("CIN Description")

        .Orientation = xlRowField

        .Position = 5

    End With

    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _

        "PivotTable4").PivotFields("Difference"), "Sum of Difference", xlSum

    ActiveSheet.PivotTables("PivotTable4").PivotFields("ASR").Subtotals = Array( _

        False, False, False, False, False, False, False, False, False, False, False, False)

    ActiveSheet.PivotTables("PivotTable4").PivotFields("ASR").LayoutForm = _

        xlTabular

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer #").Subtotals = _

        Array(False, False, False, False, False, False, False, False, False, False, False, False)

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer #").LayoutForm = _

        xlTabular

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer Name").Subtotals _

        = Array(False, False, False, False, False, False, False, False, False, False, False, False _

        )

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Customer Name").LayoutForm _

        = xlTabular

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Original Invoice"). _

        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _

        False, False)

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Original Invoice"). _

        LayoutForm = xlTabular

    ActiveSheet.PivotTables("PivotTable4").PivotFields("CIN Description"). _

        Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _

        False, False)

    ActiveSheet.PivotTables("PivotTable4").PivotFields("CIN Description"). _

        LayoutForm = xlTabular

    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum of Difference")

        .NumberFormat = "$#,##0.00_);Red"

    End With

    Range("A3").Select

    ActiveSheet.PivotTables("PivotTable4").CompactLayoutRowHeader = "ASR"

    Range("F3").Select

    ActiveSheet.PivotTables("PivotTable4").DataPivotField.PivotItems( _

        "Sum of Difference").Caption = "Savings To Customer"

    Cells.Select

    Cells.EntireColumn.AutoFit

    Range("A1").Select

End Sub

Answer 1

If you have a valid table on the ActiveSheet then the code is OK.

However, you refer to Pivot Tables in the sub name and elsewhere in your code so I am assuming that you mean the following because Tables and Pivot Tables are 2 different objects.

dataname = ActiveSheet.PivotTables(1).Name