[[20200522174529]]　『VBA　可変ファイルにピボットテーブル作成』（ピノ）　ページの最後に飛ぶ

『VBA　可変ファイルにピボットテーブル作成』（ピノ）

いつも参考にさせて頂いております。

VBA初心者です。
ピボットテーブルを作成しています。

毎回、違うファイルにピボットテーブルを作成したい為、
ピボット更新ではなく1からピボット作成をしようと思っています。

以下、記録で1から作成し、
毎回表範囲を取得できるように作成してみたのですが、
下記部分でエラーになってしまいます。

★エラー箇所

    With ActiveSheet.PivotTables("ピボットテーブル2")

※エラー内容
実行時エラー番号1004
WorkSheetクラスのPivottableプロパティを取得できません。

ピボットキャッシュまではうまくいくのですが…。
なぜだかわかりましたら教えてください。

なお、アクティブワークブックは、毎回違うので指定していません。

Sub ピボットテーブル作成()

     '変数宣言（１行列目の最終行列取得）
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column

    Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "店別データ!R1C1:R" & LR & "C" & LC, Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R3C1", TableName:="ピボットテーブル2, DefaultVersion:=6"

    Sheets("Sheet1").Select
    Cells(3, 1).Select

’★エラー箇所

    With ActiveSheet.PivotTables("ピボットテーブル2")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("出荷日　")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品コード")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品名カナ　　　　　　　　　")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ケース入数")
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ボール入数")
        .Orientation = xlRowField
        .Position = 5
    End With
    ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル2").PivotFields("引当数個数"), "合計 / 引当数個数", xlSum
    Columns("A:A").ColumnWidth = 11.1

    Range("B4").Select
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("出荷日　").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("入力年月日").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("入力時間").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("伝票ＮＯ").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("専伝ＮＯ　　").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("配送ルートＮＯ").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("配送順位").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("企業コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("得意先管理コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("得意先店舗コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("得意先コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("得意先名カナ　　　　　　　　　").Subtotals _
        = Array(False, False, False, False, False, False, False, False, False, False, False, False _
        )
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("物流センタコード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("出庫物流センタコード").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品名カナ　　　　　　　　　").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ケース入数").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ボール入数").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("入力数量　").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("数量単価区分").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("引当数個数").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ＥＯＳ区分").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("伝票区分").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("入力担当コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("仕入先コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ＪＡＮ商品コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ＩＴＦ商品コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("品切れ区分").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("単価　　　").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("売上金額　　").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("店頭売価").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("相手商品コード").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ロケーションコード").Subtotals = Array _
        (False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("摘要　　　　　　　　").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("  ").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    Columns("A:F").Select

End Sub

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

↓でも言ったけど、一からマクロで作るよりもひな形を転記して、データベースの更新したほうが楽だと思いますけどねぇ・・・
[[20200320225733]]　『VBAデータに該当得意先があったら、以下処理を行いたい』（ピノ）

（もこな２） 2020/05/22(金) 18:13

＞毎回、違うファイルにピボットテーブルを作成したい為、

毎回、同じ点、異なる点を説明してください。

（マナ） 2020/05/22(金) 19:39

もこな2様
いつもありがとうございます。
以前教えていただいたデータベース更新、と思ったのですが、
毎回ピボットテーブルを作成するファイルが異なるため、
１から作成することを検討しました。
こう言った場合でも、データベース更新を使用する事は可能でしょうか。
（ピノ） 2020/05/23(土) 11:03

マナ様
いつもありがとうございます。
説明不足で申し訳ありません。

毎回同じ点…ピボットを作成するファイルが存在するフォルダ

異なる点…ピボットを作成するファイル

最終的にはRPAで本作業を行うため、
システムで抽出したファイルを参照して、
エクセルマクロでピボットテーブルを作成したいと思ってます。　
（ピノ） 2020/05/23(土) 11:23

では、ピボット作成の元データとなるシートについて

 １）シート名は同じですか
 ２）データの列数は同じですか
 ３）データの行数は同じですか
 ４）見出し名は同じですか
 ５）見出しの順番は同じですか

まずは、ここまで教えて下さい。

（マナ） 2020/05/24(日) 10:01

マナ様

有難うございます。
３）行数以外は毎回固定になります。

 １）シート名は同じですか
…毎回同じです。

 ２）データの列数は同じですか
…毎回同じです。

 ３）データの行数は同じですか
…毎回変わります。

 ４）見出し名は同じですか
…毎回同じです。

 ５）見出しの順番は同じですか
…毎回同じです。
（ピノ） 2020/05/25(月) 10:05

では次に、ピボットテーブルの挿入先のシートについて

 ６）新規シートですか、既存のシートですか。
 ７）既存のシートの場合、シート名は決まっていますか。

（マナ） 2020/05/25(月) 19:45

マナ様
何度もありがとうございます。
ピボットテーブルの挿入先のシートは、新規シートです。
（ピノ） 2020/05/25(月) 20:53

では、マクロ実行前のブックのシート構成について

 ８）元データのシートの他にも、シートはありますか。

（マナ） 2020/05/25(月) 21:04

マナ様

有難う御座います。
マクロ実行前のブックに元データの他にシートはありません。
（ピノ） 2020/05/26(火) 18:10

 ９）１度のマクロ実行で処理するファイルは、１つだけですか。
 10）マクロは、ピボット作成して、それで終わりですか。

（マナ） 2020/05/26(火) 18:27

マナ様
有難う御座います。
9)1つだけです。
10)ピボットテーブルを作成して終わりになります。
（ピノ） 2020/05/26(火) 22:18

では、雛形ブックを手作業で作成しておき、
こんな感じのマクロでどうですか。
マクロでは、雛形ブックのデータを入れ替えているだけです。
なので、全部手作業でも、手間はそんなにかわりません。

 （準備：雛形ブックの作成）
 １）新規ブックに 何でもよいの適当な元データを用意する
 ２）手作業で、１）を元に、ピボットを作成する。
 ３）元データのシート名を「data」に変更する
 ４）ピボットのシート名を「pvt」に変更する
 ５）下記のマクロを、標準モジュールにコピペする
 ６）これを雛形ブック（マクロブック）として保存する

 （マクロ実行）
 １）雛形ブックを開く
 ２）Alt+F8からマクロを選んで実行
 ３）必要なら、雛形ブックを別名保存（手作業またはマクロで）

 Sub test()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim pvt As PivotTable

    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Sub
        End If
    End With

    Set wsData = ThisWorkbook.Sheets("data")
    Set pvt = ThisWorkbook.Sheets("pvt").PivotTables(1)

    wsData.UsedRange.Clear
    wb.Sheets(1).UsedRange.Copy wsData.Cells(1)
    pvt.SourceData = wsData.Cells(1).CurrentRegion.Address(, , xlR1C1, True)
    wb.Close False

 End Sub

（マナ） 2020/05/26(火) 22:41

マナ様
連絡が遅くなり申し訳ありません。

ご丁寧にご教授いただきありがとうございます。
試してみたいと思います！！！
なんどもなんどもすみませんでした。助かりました。
（ピノ） 2020/05/28(木) 22:22

コメント返信：

[ 一覧(最新更新順) ]

[[20200522174529]] 『VBA 可変ファイルにピボットテーブル作成』（ピノ） ページの最後に飛ぶ

[[20200522174529]]　『VBA　可変ファイルにピボットテーブル作成』（ピノ）　ページの最後に飛ぶ