[[20200617123437]]　『ピボットテーブルのデータ範囲再設定をVBAで』（じょあ）　ページの最後に飛ぶ

『ピボットテーブルのデータ範囲再設定をVBAで』（じょあ）

お世話になっております。
ピボットテーブルのデータ範囲を変更したく、ネットで検索して以下のように書いてみたのですがデータソースの指定がうまくいきません。ご助言いただけますでしょうか。
データは"Data"という名前のワークシートのA1からデータが入っている範囲を選択したいです。(列数はQ列目までで固定ですが行数が変わります)

Sub データソースの変更()

    Dim wsPivot As Worksheet   'ピポットテーブルのシート
    Dim Ws As Worksheet   'データ元のシート名
    Dim ptblName As String     'ピポットテーブル名
    Dim sourceArea As String   'データ範囲
    Set wsPivot = Worksheets("差分確認")
    Set Ws = Worksheets("Data")
    ptblName = "差分確認table"
    sourceArea = Ws.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)

  'ピポットテーブルのデータソース変更
    wsPivot.PivotTables(ptblName).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=Ws & "!" & sourceArea)
End Sub

＜使用 Excel：Office365、使用 OS：Windows10 ＞

回答者にデバッグさせるつもりですか？ご自身でステップ実行し、どの行でどんなエラーメッセージが表示されるかを調べて、書きだしてください。

間違っていそうなところなぞ。
・With文を宣言していないのに、親シートを省略して .Cells とか書いてある。親は誰？
・sourceAreaは文字列変数なのに、Rangeオブジェクトを代入している。欲しいのは .Address では？
（???） 2020/06/17(水) 14:03

もう1か所。
・SourceData:=Ws &…、と書いているが、WsはWorkSheetオブジェクト。欲しいのは .Name？
（???） 2020/06/17(水) 14:06

意味も分からず切り貼りで作ったものでこのようになってしまい、大変失礼いたしました。
申し訳ございませんでした。
ご指摘のSourceAreaの指定はやめ、シートと範囲を指定してみたのですが最後の三行が構文エラーになってしまいます。
今は仮でQ列100行目にしているのですが本当は1万行以上あって、都度変わるので最終行まで取りたいのです。
ご助言をお願いできますでしょうか。

Sub データソースの変更()

    Dim wsPivot As Worksheet   'ピポットテーブルのシート
    Dim Ws As Worksheet   'データ元のシート名
    Dim ptblName As String     'ピポットテーブル名
    Set wsPivot = Worksheets("差分確認")
    Set Ws = Worksheets("Data")
    ptblName = "差分確認table"
  'ピポットテーブルのデータソース変更
    wsPivot.PivotTables(ptblName).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:= Ws.Range("A1:Q100")
End Sub
（じょあ） 2020/06/17(水) 14:24

 カッコが閉じてないだけでは？
 少なくとも構文エラーくらいはご自分で確認して下さい。

 必要な行だけ列挙すると、こんなことでしょうか。
    Dim sourceArea As Range
    Set sourceArea = Ws.Range("A2:Q" & Ws.Cells(Rows.Count, "A").End(xlUp).Row)
    wsPivot.PivotTables(ptblName).ChangePivotCache _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=sourceArea)

（γ） 2020/06/17(水) 14:48

γ様
ご助言ありがとうございました。実行してみたところ、既存のPivot tableの設定が消えてしまったようです。
該当Worksheetに「ピボットテーブルを操作するには、ピボットテーブルのフィールドリストからフィールドを選択してください」となります。
設定はそのままに、データ範囲だけ変更させる方法はないでしょうか。
（じょあ） 2020/06/17(水) 15:09

簡単な例でマクロ記録をとって確認して下さい。
（γ） 2020/06/17(水) 15:12

γ様
ご返信ありがとうございます。
最初にマクロの記録をやってみました。今も再度やってみたのですが、データソースの変更は記録されませんでした。
（じょあ） 2020/06/17(水) 16:00

手動で記録されないなんてはずはないので、おそらく何か間違えてますよ。

シート指定がうまくできていないような気がするので、直接、プロパティを変更しては？ SourceDataプロパティは、文字列で指定します。（何で途中でRangeオブジェクトに変えてしまったのやら…）

    wsPivot.PivotTables(ptblName).SourceData = Ws.Range("A1:Q" & Ws.Cells(Rows.Count, "A").End(xlUp).Row).Address(1, 1, xlR1C1, True)
（???） 2020/06/17(水) 16:43

???様
ご記載頂きましたCodeで無事データ範囲のみ変更させる事ができました。
早々にアドバイス頂きありがとうございました。質問の仕方も含め、大変勉強になりました。
（じょあ） 2020/06/17(水) 17:32

 バージョン依存でしたか。ミスリードしてしまったようですね。

 私の手元のVersion(Excel2010)では、
 Sub Macro1()
     Dim r As Range
     Set r = Range("Sheet1!A1:B7")
     ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").ChangePivotCache ActiveWorkbook. _
         PivotCaches.Create(SourceType:=xlDatabase, SourceData:=r, _
         Version:=xlPivotTableVersion14)
 End Sub
 が動作します。

 また、PivotCaches.Createのヘルプには、以下の記述があり、RangeオブジェクトでOKのようにあります。
 | SourceType が xlExternal ではない場合は、引数 SourceData を必ず指定します。
 | この引数には、Range オブジェクト (SourceType が xlConsolidation または xlDatabase の場合)
 | または Excel ブックの Connection オブジェクト (SourceType が xlExternal の場合) を
 | 指定できます。

（γ） 2020/06/17(水) 17:35

2019で確認したら確かに変わっていました。

 以下、ヘルプの引用です。

 SourceType が xlExternal でない場合は、SourceData 引数が必要です。 
 このメソッドは、 Range オブジェクト ( _SourceType_がxlconsolidationまたはxlconsolidationの場合) 
 または**WorkbookConnection** オブジェクト ( _SourceType_がxlconsolidationの場合) に渡される必要があります。

 rangeオブジェクトを渡す場合は、ブック、ワークシート、およびセル範囲を指定する
 ために、文字列を使用するか、名前付き範囲を設定して名前を文字列として渡すことを
 お勧めします。 Rangeオブジェクトを渡すと、予期しないエラーが発生することが
 あります。

（γ） 2020/06/17(水) 17:44

γ様
検証までしていただきありがとうございました。
バージョンによっても異なってしまうのですね。
親切にヘルプまで引用頂きまして大変恐縮です。
当方素人なためネットで検索してはコピペして、の繰り返しをしていただため、引用するにもよく注意するように致します。
この度は私の面倒な質問に最後までお付き合いいただき本当にありがとうございました。
（じょあ） 2020/06/17(水) 17:56

コメント返信：

[ 一覧(最新更新順) ]

[[20200617123437]] 『ピボットテーブルのデータ範囲再設定をVBAで』（じょあ） ページの最後に飛ぶ

[[20200617123437]]　『ピボットテーブルのデータ範囲再設定をVBAで』（じょあ）　ページの最後に飛ぶ