Thread: Excel 2007 pivot table/macro/Runtime error 1004

Hi
I have created a macro that works sporadically. Here is the code below. When I try to run it I get

runtime error 1004
Unable to set the ShowDetail property of the RangeClass

This is the actual code of the macro. The debugger points to the last line of it.

   ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
        Array("Prodn_Plan_Prep!R1C1:R2000C18"), Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Value" _
        ).Position = 1
    Range("B3").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Column").Name = "mmm-yy"
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 59
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 81
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 124
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 135
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 159
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 185
    ActiveWindow.ScrollRow = 196
    ActiveWindow.ScrollRow = 207
    ActiveWindow.ScrollRow = 222
    ActiveWindow.ScrollRow = 233
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 254
    ActiveWindow.ScrollRow = 263
    ActiveWindow.ScrollRow = 274
    ActiveWindow.ScrollRow = 289
    ActiveWindow.ScrollRow = 304
    ActiveWindow.ScrollRow = 315
    ActiveWindow.ScrollRow = 330
    ActiveWindow.ScrollRow = 347
    ActiveWindow.ScrollRow = 363
    ActiveWindow.ScrollRow = 378
    ActiveWindow.ScrollRow = 388
    ActiveWindow.ScrollRow = 397
    ActiveWindow.ScrollRow = 406
    ActiveWindow.ScrollRow = 414
    ActiveWindow.ScrollRow = 423
    ActiveWindow.ScrollRow = 430
    ActiveWindow.ScrollRow = 436
    ActiveWindow.ScrollRow = 445
    ActiveWindow.ScrollRow = 453
    ActiveWindow.ScrollRow = 460
    ActiveWindow.ScrollRow = 466
    ActiveWindow.ScrollRow = 473
    ActiveWindow.ScrollRow = 482
    ActiveWindow.ScrollRow = 486
    ActiveWindow.ScrollRow = 490
    ActiveWindow.ScrollRow = 495
    ActiveWindow.ScrollRow = 501
    ActiveWindow.ScrollRow = 505
    ActiveWindow.ScrollRow = 510
    ActiveWindow.ScrollRow = 514
    ActiveWindow.ScrollRow = 518
    ActiveWindow.ScrollRow = 523
    ActiveWindow.ScrollRow = 527
    ActiveWindow.ScrollRow = 534
    ActiveWindow.ScrollRow = 538
    ActiveWindow.ScrollRow = 544
    ActiveWindow.ScrollRow = 549
    ActiveWindow.ScrollRow = 555
    ActiveWindow.ScrollRow = 559
    ActiveWindow.ScrollRow = 566
    ActiveWindow.ScrollRow = 572
    ActiveWindow.ScrollRow = 579
    ActiveWindow.ScrollRow = 585
    ActiveWindow.ScrollRow = 592
    ActiveWindow.ScrollRow = 598
    ActiveWindow.ScrollRow = 605
    ActiveWindow.ScrollRow = 611
    ActiveWindow.ScrollRow = 618
    ActiveWindow.ScrollRow = 624
    ActiveWindow.ScrollRow = 629
    ActiveWindow.ScrollRow = 633
    ActiveWindow.ScrollRow = 642
    ActiveWindow.ScrollRow = 650
    ActiveWindow.ScrollRow = 659
    ActiveWindow.ScrollRow = 668
    ActiveWindow.ScrollRow = 681
    ActiveWindow.ScrollRow = 692
    ActiveWindow.ScrollRow = 702
    ActiveWindow.ScrollRow = 713
    ActiveWindow.ScrollRow = 722
    ActiveWindow.ScrollRow = 728
    ActiveWindow.ScrollRow = 735
    ActiveWindow.ScrollRow = 743
    ActiveWindow.ScrollRow = 750
    ActiveWindow.ScrollRow = 756
    ActiveWindow.ScrollRow = 763
    ActiveWindow.ScrollRow = 769
    ActiveWindow.ScrollRow = 776
    ActiveWindow.ScrollRow = 782
    ActiveWindow.ScrollRow = 787
    ActiveWindow.ScrollRow = 791
    ActiveWindow.ScrollRow = 795
    ActiveWindow.ScrollRow = 800
    ActiveWindow.ScrollRow = 804
    ActiveWindow.ScrollRow = 811
    ActiveWindow.ScrollRow = 815
    ActiveWindow.ScrollRow = 819
    ActiveWindow.ScrollRow = 821
    ActiveWindow.ScrollRow = 824
    ActiveWindow.ScrollRow = 828
    ActiveWindow.ScrollRow = 830
    ActiveWindow.ScrollRow = 832
    ActiveWindow.ScrollRow = 837
    ActiveWindow.ScrollRow = 839
    ActiveWindow.ScrollRow = 843
    ActiveWindow.ScrollRow = 845
    ActiveWindow.ScrollRow = 852
    ActiveWindow.ScrollRow = 854
    ActiveWindow.ScrollRow = 858
    ActiveWindow.ScrollRow = 860
    ActiveWindow.ScrollRow = 863
    ActiveWindow.ScrollRow = 865
    ActiveWindow.ScrollRow = 867
    ActiveWindow.ScrollRow = 869
    ActiveWindow.ScrollRow = 871
    ActiveWindow.ScrollRow = 873
    ActiveWindow.ScrollRow = 878
    ActiveWindow.ScrollRow = 882
    ActiveWindow.ScrollRow = 886
    ActiveWindow.ScrollRow = 891
    ActiveWindow.ScrollRow = 897
    ActiveWindow.ScrollRow = 904
    ActiveWindow.ScrollRow = 910
    ActiveWindow.ScrollRow = 912
    ActiveWindow.ScrollRow = 917
    ActiveWindow.ScrollRow = 919
    ActiveWindow.ScrollRow = 921
    ActiveWindow.ScrollRow = 923
    ActiveWindow.ScrollRow = 927
    ActiveWindow.ScrollRow = 932
    ActiveWindow.ScrollRow = 936
    ActiveWindow.ScrollRow = 943
    ActiveWindow.ScrollRow = 949
    ActiveWindow.ScrollRow = 953
    ActiveWindow.ScrollRow = 956
    ActiveWindow.ScrollRow = 958
    ActiveWindow.ScrollRow = 960
    ActiveWindow.ScrollRow = 962
    ActiveWindow.ScrollRow = 964
    ActiveWindow.ScrollRow = 966
    ActiveWindow.ScrollRow = 973
    ActiveWindow.ScrollRow = 975
    ActiveWindow.ScrollRow = 979
    ActiveWindow.ScrollRow = 982
    ActiveWindow.ScrollRow = 984
    ActiveWindow.ScrollRow = 986
    ActiveWindow.ScrollRow = 988
    ActiveWindow.ScrollRow = 990
    ActiveWindow.ScrollRow = 992
    ActiveWindow.ScrollRow = 997
    ActiveWindow.ScrollRow = 1003
    ActiveWindow.ScrollRow = 1005
    ActiveWindow.ScrollRow = 1008
    ActiveWindow.ScrollRow = 1012
    ActiveWindow.ScrollRow = 1014
    ActiveWindow.ScrollRow = 1018
    ActiveWindow.ScrollRow = 1021
    ActiveWindow.ScrollRow = 1023
    ActiveWindow.ScrollRow = 1025
    ActiveWindow.ScrollRow = 1027
    ActiveWindow.ScrollRow = 1031
    ActiveWindow.ScrollRow = 1034
    ActiveWindow.ScrollRow = 1036
    ActiveWindow.ScrollRow = 1038
    ActiveWindow.ScrollRow = 1040
    ActiveWindow.ScrollRow = 1042
    ActiveWindow.ScrollRow = 1044
    ActiveWindow.ScrollRow = 1047
    ActiveWindow.ScrollRow = 1049
    ActiveWindow.ScrollRow = 1053
    ActiveWindow.ScrollRow = 1055
    ActiveWindow.ScrollRow = 1057
    ActiveWindow.ScrollRow = 1060
    ActiveWindow.ScrollRow = 1064
    ActiveWindow.ScrollRow = 1070
    ActiveWindow.ScrollRow = 1077
    ActiveWindow.ScrollRow = 1083
    ActiveWindow.ScrollRow = 1090
    ActiveWindow.ScrollRow = 1094
    ActiveWindow.ScrollRow = 1096
    ActiveWindow.ScrollRow = 1099
    ActiveWindow.ScrollRow = 1103
    ActiveWindow.ScrollRow = 1105
    Range("S20000").Select
    Selection.ShowDetail = True
End Sub

Is cell S20000 actually part of your pivot table?