Hi ,

I want a macro for creating pivot table.However I get an error after a
certain comand (indicated by------)

Following is the set of commands I have typed -

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

----------------------

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

Can any one help me with this?

You're getting the error because Sheet1 isn't always the next sheet if
you're running this multiple times.
You probably need to add the first 3 lines below somewhere in the
beginning of your code to delete the "old" Pre Checks (Assuming you
don't need it anymore).
Then replace your

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pre Checks"
with
ActiveSheet.Name = "Pre Checks"



Code:
On Error Resume Next
Sheets("Pre Checks").Delete
On Error GoTo 0


ActiveSheet.Name = "Pre Checks"


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570770

Thnx for the help.
However the sheet I am trying to rename is not an existing sheet. It gets
created from pivot table. It is always "SHEET1". I have been careful about
it. I tried to use the commands as given by you but I again land up with
the same error.

"pikapika13" wrote:


You're getting the error because Sheet1 isn't always the next sheet if
you're running this multiple times.
You probably need to add the first 3 lines below somewhere in the
beginning of your code to delete the "old" Pre Checks (Assuming you
don't need it anymore).
Then replace your

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pre Checks"
with
ActiveSheet.Name = "Pre Checks"



Code:
On Error Resume Next
Sheets("Pre Checks").Delete
On Error GoTo 0


ActiveSheet.Name = "Pre Checks"


--
pikapika13
------------------------------------------------------------------------
pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892
View this thread: http://www.excelforum.com/showthread...hreadid=570770