Excel VBA ピボットテーブル2013で動作するけど2007だと動作しない

Question

Windows7です。
以下ピボットテーブルの記述で2013で動作しますが2007だと動作しません。
しかし2007でもF8で1行づつだと動作しますどうしたら2007で動作するようになるのでしょうか？

'～
    With ActiveSheet
        Set k = .Range("Y3", .Cells(.Rows.Count, 1).End(xlUp))
    End With
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
                                        SourceData:=k.Address(external:=True))
        With .CreatePivotTable(TableDestination:="")　　　'←ここでエラーストップします。
            .DisplayNullString = False
                   
            .AddFields RowFields:="ランク名", _
                       ColumnFields:="開始月"
            With .PivotFields("売上/月")
                          
                .Orientation = xlDataField
                .Caption = "合計 : 金額"
                .Function = xlSum
                .NumberFormat = "#,##0_ ;[red]-#,##0 "
            End With
        End With
    End With
    Set k = Nothing
'～

WindFaller · Accepted Answer

こんにちは。

> Worksheets.Add After:=ActiveSheet
とした場合もsheet5に作成されました？
この件は、突っ込まれないように触れないでおいたのですが、バレとしまいましたね。ちょっと、難しい説明をしなければならなくなるからです。

Worksheets.Add After:=ActiveSheet
これでできたものを、インスタンスといいます。たい焼きと、その金型とになぞらえれます。金型のことをクラスといい、そして、たい焼きのほうを、インスタンスといいます。

一度作ると、仮想ファイル上に作ったという印が残ります。だから、その作ったものを削除しても、その痕跡を数えて、1,2,3,……と続きます。
Sheet4のシートを削除しても、数えていきますから、Sheet5 となるわけです。もし、次の数字を取る場合は、マクロが数えてあげなくてはなりません。

Worksheets.Add After:=Worksheets(Sheets.Count)
ActiveSheet.Name = "Sheet" & Worksheets.Count

もしも、シートの数が合わないようでしたら、隠しシートが存在することになります。また、途中のシートが抜けている時は、おそらく、既存のシート名とぶつかってしまうので、

実行時エラー '1004'
「この名前はすでに使用されています。別の名前を入力してください。」
というエラーが出てきます。そうすると、マクロとしては、とてもややこしくなってしまいますので、ユニークな名前を予め決めておけば、簡単に済むと考えたわけなのです。

WindFaller · Answer

こんばんは。

>しかしsheet5に作成されました？最初のsheet設定数は3です。
>試しに最初のsheet設定数を1にするとsheet3に作成されました？

これに関しては、私は成功するかどうかわからなかったので、深くは書きませんでしたが、それは自由に管理可能です。

どこに置くか？データシートの右隣なら、
Worksheets.Add After:=ActiveSheet 
左隣なら、
Worksheets.Add Before:=ActiveSheet
左の先頭なら、
Worksheets.Add Before:=Worksheets(1)
右の最後尾は
Worksheets.Add Before:=Worksheets(Sheets.Count)
[Sheets.Count とかいてWorksheets と書かないのは、グラフシートなどは、Worksheet ではないから、それらを含めると、Sheets になります。]

シート名については、シート番号のようなものですと、同名でエラーになることもあるので、例えば、PivotSheet1 としておきます。

'～'
Worksheets.Add After:=ActiveSheet
　　ActiveSheet.Name = "PivotData1"
　　.CreatePivotTable TableDestination:=Range("A1"), _
　　　 TableName:="PivotTable1"
End With
と書きます。

WindFaller · Answer

#2の回答者です。

もう一度、調べてみました。
ピボットは、それそのものがマクロですので、とても扱いにくいのですが、以下の違いを考えてみました。
>2013で動作しますが
>2007だと動作しません。+2007でもF8で1行づつだと動作します

2013は、マクロの動作が遅いということにつきます。
それと、このマクロは新規のシートにピボットを展開するようになっているかと思います。

そこで、
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=k.Address(external:=True))
Worksheets.Add　　'←この行を入れる
　.CreatePivotTable TableDestination:=Range("A1"), _
　　　　TableName:="PivotTable1"
End With　　　　
With ActiveSheet.CreatePivotTables(1)

End Wtih

'//
なお、
Set k = .Range("Y3", .Cells(.Rows.Count, 1).End(xlUp))
確認ですが、これは逆向きで、Y3から、A列の範囲の終わりを探すようになっていますが、それは間違いないでしょうね。

WindFaller · Answer

こんにちは。

ヘルプを見てみましたが、
With .CreatePivotTable(TableDestination:="")

TableDestination:=""　ではエラーがでるはずです。

ヘルプでは、
「TableDestination 必須 
バリアント型 (Variant) ピボットテーブル レポートの配置先範囲 (結果のピボットテーブル レポートを配置するワークシート上の範囲) の左上端のセルを指定します。」
となっています。

例：
 .CreatePivotTable TableDestination:=Range("A3"), TableName:= "PivotTable1"

tatsu99 · Answer

全くのあてずっぽうで申し訳ありませんが、
エラーとなる個所の前後に、DoEventsを入れてみてはいかがでしょうか。
今回なら

DoEvents
With .CreatePivotTable(TableDestination:="")　　　'←ここでエラーストップします。
DoEvents

のようになるかと思います。
DoEventsは他のスレッドの為にCPUを解放するのが目的ですが、それ以上のことは詳しくは判っていません。
はっきりした目的がある以外は使用しないほうが良いとされていますが、今回のようなケースの場合、
DoEventsで解決する可能性もあります。

Excel VBA ピボットテーブル2013で動作するけど2007だと動作しない

こんにちは。

こんばんは。

#2の回答者です。

こんにちは。

全くのあてずっぽうで申し訳ありませんが、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング