Runtime error at #dataname = ActiveSheet.ListObject(1).Name#

[Skopweb]

Hi - while executing macro, i receive a runtime error at
#dataname = ActiveSheet.ListObject(1).Name#
any suggestions?

Below is the script:
============================================
#
Sub testingmacro()
'
' testingmacro Macro
' For Pivot
'


'


Dim dataname As String
Dim newsheet As String




Columns("A:O").Select

dataname = ActiveSheet.ListObject(1).Name

Sheets.Add
newsheet = ActiveSheet.Name

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataname, Version:=6).CreatePivotTable TableDestination:= _
newsheet & "!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets(newsheet).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("request.u_campaign")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("assignment_group")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("number"), "Count of number", xlCount
End Sub
#
=====================================================

[p45cal]

dataname = ActiveSheet.ListObjects(1).Name

[Skopweb]

Thank-you p45ca (http://www.vbaexpress.com/forum/member.php?3494-p45cal)l. However, i'm still facing the same error.

[p45cal]

A guess (more info needed):
Sub testingmacro()
Dim dataname As String
Dim newsheet As String

Columns("A:O").Select

dataname = ActiveSheet.ListObjects(1).Name

Sheets.Add
newsheet = ActiveSheet.Name

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=dataname, Version:=6).CreatePivotTable(TableDestination:=newsheet & "!R3C1", DefaultVersion:=6)
With .PivotFields("request.u_campaign")
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields("assignment_group")
.Orientation = xlRowField
.Position = 1
End With
.AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("number"), "Count of number", xlCount
End With
End Sub