Hi all

I have a code with which reads the data from sheet 1 and creates a pivot table on sheet2. This code works. Now I want to copy some data from sheet1 to sheet4 and create the same pivot onto sheet2. This does not work! I get this error message Run Time Error 1004. I used F8 to run the code line by line, i get this error when i run the line below in the code starting with activeworkbook.pivotcaches.........etc

Sub CommandButton1_Click()
Dim Temp As Long
Dim Temp1 As Long
Dim Temp2 As Long

    
 Sheets("Sheet2").Select
 Sheets("Sheet2").Range("A1:P500").ClearContents

 Sheets("Sheet1").Select
 ActiveSheet.Range("C6:E7").Select
 Selection.Copy
  
 ActiveSheet.Paste Destination:=Worksheets("Sheet4").Range("C100")
  
 Sheets("Sheet4").Select
 ActiveSheet.Range("D1:E2000").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet4!R1C4:R2000C5").CreatePivotTable TableDestination:=Sheets("Sheet2").Cells(3, 1), TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    'ActiveSheet.PivotTableWizard TableDestination:=Sheets("Sheet2").Cells(3, 1)
    'Sheets("Sheet2").Cells(3, 1).Select
    With Sheets("Sheet2").PivotTables("PivotTable2")
        .ColumnGrand = False
        .RowGrand = False
        .NullString = "0"
    End With
    Sheets("Sheet2").PivotTables("PivotTable2").AddFields RowFields:= _
        "Failure Category", ColumnFields:="Ship month"
    Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("Ship month").Orientation = _
        xlDataField
        
    ActiveSheet.Rows("4:4").Select
    Selection.NumberFormat = "[$-409]mmm-yy;@"
    
    With Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("Ship month")
        .PivotItems("(blank)").Visible = False
    End With
    With Sheets("Sheet2").PivotTables("PivotTable2").PivotFields("Failure Category")
        .PivotItems("(blank)").Visible = False
        
    End With
----------------------------------------------

Please help me out.

Thanks in advance
Keshav