Hi,

I want a macro for creating a pivot table.
Pivot table has one row field, one column field and one data field (where I
want a count of data). After this I want to rename the pivot table new sheet.
However it give me an error called "subscript out of range" & "Run time
error 9".
Can any one please help me with this?

Many Thanks

You can record a macro as you create the pivot table and then assign the
macro to a button for future use.

"anu" wrote:

Hi,

I want a macro for creating a pivot table.
Pivot table has one row field, one column field and one data field (where I
want a count of data). After this I want to rename the pivot table new sheet.
However it give me an error called "subscript out of range" & "Run time
error 9".
Can any one please help me with this?

Many Thanks

I tried this. It works well till the time pivot tabel is created. But as soon
as I try to rename the sheet I get an error. After when I try to rename the
pivot table sheet I get error as mentioned below.Find below the command set
which I used.

Sheets("Final Sheet").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Final Sheet'!R1C1:R1567C43").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("CODE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Type"), "Count of Type", xlCount
Range("A3").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Count of Type"). _
Orientation = xlHidden
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Type")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables
("PivotTable2").PivotFields("CTRY"), "Count of CTRY", xlCount

' HERE I GET AN ERROR IN THE LINE BELOW'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pre Checks"
Range("A5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("CHECKS").Select
Range("A2").Select
ActiveSheet.Paste



"Dave F" wrote:

You can record a macro as you create the pivot table and then assign the
macro to a button for future use.

"anu" wrote:

Hi,

I want a macro for creating a pivot table.
Pivot table has one row field, one column field and one data field (where I
want a count of data). After this I want to rename the pivot table new sheet.
However it give me an error called "subscript out of range" & "Run time
error 9".
Can any one please help me with this?

Many Thanks