Skopweb
07-07-2018, 02:15 AM
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
#
=====================================================
#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
#
=====================================================