[[20200308010329]]　『VBAピボットテーブル可変範囲設定方法』（なみ）　ページの最後に飛ぶ

『VBAピボットテーブル可変範囲設定方法』（なみ）

はじめまして。VBA初心者質問させてください。

マクロでピボットテーブルを作成したいのですが、
毎回データの行数が変わるため、
可変範囲取得の構文を、どのように記載したらいいか教えてください。

以下、マクロ記録でとったものですが、
月次実績ファイルの行：「R3171」が毎回変わりますです。
※列：「C43」は固定です。

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "=月次実績"!R1C1:R3171C43", Version:=6).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1", DefaultVersion:= _
        6

よろしくお願いいたします。

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

 ＞以下、マクロ記録でとったものですが、

 ホントですか？
 こちらでは構文エラーになりますけども。

 ＞月次実績ファイルの行：「R3171」が毎回変わりますです。

 ＞"=月次実績"!R1C1:R3171C43"
　　　　↓
 　 "月次実績!R1C1:R" & Sheets("月次実績").Cells(Rows.Count, 43).End(xlUp).Row & "C43"

 ※ 左端の等号(=)は無し

（半平太） 2020/03/08(日) 14:34

半平太様

ご教授有難うございます。

マクロ記録の構文、記載が間違っておりました。
正しくは以下の通りです。失礼いたしました。

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

        "月次実績!R1C1:R3171C40", Version:=6).CreatePivotTable _
        TableDestination:="Sheet2!R3C1", tablename:="ピボットテーブル1", DefaultVersion:= _
        6
アドバイスいただいた構文に差し替えてみましたが、
インデックス範囲エラーになってしまいました。
差し替え箇所部分前後の構文で間違っている部分、教えていただけないでしょうか。
何度も申し訳ございません。

        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "月次実績!R1C1:R" & Sheets("月次実績").Cells(Rows.Count, 43).End(xlUp).Row & "C43", Version:=6).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", tablename:="ピボットテーブル1", DefaultVersion:= _
        6

（なみ） 2020/03/09(月) 00:02

質問を勘違いしてるのかもしれませんけど、ビボットテーブルの「データソースの変更」をしたいということでしょうか？

そうであれば、↓が参考になりませんか？
[[20200121175436]]　『マクロでcsvの取り込みからピポットテーブルの更新までを自動化』（あやとん）

要は【シート名含めてセル番地】を【文字列】で取得しておいてから、SourceDataに与えればいいんじゃないかと・・

あと、アドレスはA1参照形式でも問題なかったような気が・・

そもそも質問を勘違いしていたらごめんなさい。

（もこな２） 2020/03/09(月) 03:54

もこな2様
コメントありがとうございます。
説明不足で申し訳ありません。
データソースの更新は使用せず、
1から可変範囲を取得したいと思っています。

アドレスはA1参照形式でトライでも問題ありませんが、
構文の書き方がわからず、記録でとれた、
R 1C1形式にしてしまいました。。
（なみ） 2020/03/09(月) 12:36

ピボットの作成場所を、Sheet1からSheet2に変更していますが…
Sheet2が存在しますか？

（マナ） 2020/03/09(月) 19:30

ピボットテールは、１行のコードが長くなりがちなので
変数を使うとよいです。

で、Sheet2にした理由が、Sheet1だとエラーがでるということなら
挿入前に、クリアするとよいです。

 Sub test()
    Dim 元データ As Range
    Dim 作成場所 As Range
    Dim pvt As PivotTable

    Set 元データ = Worksheets("月次実績").Range("A1").CurrentRegion
    Set 作成場所 = Worksheets("Sheet1").Range("A3")
    作成場所.Worksheet.UsedRange.Clear

    Set pvt = ActiveWorkbook.PivotCaches.Create(xlDatabase, 元データ).CreatePivotTable(作成場所)

 End Sub

新規シートのA3に作成したいなら

  Sub test2()
    Dim 元データ As Range
    Dim 作成場所 As Range
    Dim pvt As PivotTable

    Set 元データ = Worksheets("月次実績").Range("A1").CurrentRegion
    Set 作成場所 = Worksheets.Add.Range("A3")

    Set pvt = ActiveWorkbook.PivotCaches.Create(xlDatabase, 元データ).CreatePivotTable(作成場所)

 End Sub

A1でよいなら

 Sub test3()
    Dim 元データ As Range
    Dim pvt As PivotTable

    Set 元データ = Worksheets("月次実績").Range("A1").CurrentRegion

    Set pvt = ActiveWorkbook.PivotCaches.Create(xlDatabase, 元データ).CreatePivotTable("")

 End Sub

↑では、引数の「元データ」は、Rangeオブジェクトにしていますが
まれにエラーが発生するので（データが多いとき？）
マクロの記録のように、アドレスで指定するほうがよいかもしれません。
ヘルプもそうなっています。

（マナ） 2020/03/09(月) 20:06

マナ様
有難うございます！！
出来てそうです！！！！

取得できた範囲を確認したいのですが
CurrentRegionの範囲を見たい場合は、
イミディエイトウィンドウで確認可能でしょうか。

確認するためのイミディエイトウィンドウに記載する構文も教えていただけたら幸いです。
TRYしてみましたが、取得できず…
何度も申し訳ありません。

（なみ） 2020/03/10(火) 00:11

 >取得できた範囲を確認したいのですが
 >CurrentRegionの範囲を見たい場合は、
 >イミディエイトウィンドウで確認可能でしょうか。

範囲の確認はAddressプロパティを見ればいいので、
イミディエイトウィンドウには、Addressプロパティの内容を表示するようにすれば、
文字列で返してくれます。
また、目視で確認したい場合は、敢えてSelectしてみるのもありかと思います。

（まっつわん） 2020/03/10(火) 10:07

まっつわん様
有難うございます。
Addressプロパティ、以下構文を書いてみました。
エラーは起きませんでしたが、
取得が意図している範囲ではなく「$A$1」となってしまいます…。
実際には、元データの範囲はA1～AM3171まであります。

構文、違っていますでしょうか？

[? Range("A1").CurrentRegion.Address]
度たびの質問で申し訳ありません。

（なみ） 2020/03/10(火) 22:22

? Worksheets("月次実績").Range("A1").CurrentRegion.Address

これで、どうなりますか。

（マナ） 2020/03/10(火) 22:35

コメント返信：

[ 一覧(最新更新順) ]

[[20200308010329]] 『VBAピボットテーブル可変範囲設定方法』（なみ） ページの最後に飛ぶ

[[20200308010329]]　『VBAピボットテーブル可変範囲設定方法』（なみ）　ページの最後に飛ぶ