[[20220108003905]] 『ピボットテーブルのコードのエラーについて』(ジョー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『ピボットテーブルのコードのエラーについて』(ジョー)

VBAでのピボットテーブルのコードの書き方について。

VBA初心者です。
ご教示のほどよろしくお願い申し上げます。
「データX」「データY」という2つのシートのあるブックにて、「データX」シート(A1〜データテーブルが掲載)のデータから、新たに作ったシート「集計」にピボットテーブル(A1〜にテーブル表示)を作成したいと考えています。

 本を参考に下記のコードを記載しましたが、「型が違う」としてエラーが出てしまいます。
 何が間違っているのでしょうか??

 Worksheets.Add(before.=Worksheets(“データX”).Name=“集計”
 Dim pc As PivotCache
 Dim pt As PivotTable

 Set pc =ActiveWorkbook.PivotCaches.Create(SourceType:= xlDatabase,SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion)

 Set pt=pc.CreatePivotTable(TableDestination:=Range(“A1”),TableName:=“PV01”)

< 使用 Excel:Office365、使用 OS:Windows10 >


参考にした、本にはどの様に書いてありましたか?
(有効) 2022/01/08(土) 01:18

 投稿する際には、コードを手打ちせずに、
 VBEからそのままコピーペイストするとよいですよ。
 そうしないと、タイプミスなのか本来の間違いなのか見分けがつきません。

 こんなコードでは動きませんか?
     Dim pc As PivotCache
     Dim pt As PivotTable
     Worksheets.Add(before:=Worksheets("データX")).Name = "集計"
     Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("データX").Range("A1").CurrentRegion)
     Set pt = pc.CreatePivotTable(TableDestination:=Range("A1"), TableName:="PV01")

 エラーになったら、どの行でエラーになったかも書くとよいですよ。
(γ) 2022/01/08(土) 08:27

 >SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion
  ↓
  SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion.Address(external:=true)

  
 https://docs.microsoft.com/ja-jp/office/vba/api/excel.pivotcaches.create
>Range オブジェクトを 渡す 場合は、
>文字列を使用してブック、ワークシート、セル範囲を指定するか、
>名前付き範囲を設定して名前を文字列として渡することをお勧めします。
>Range オブジェクトを 渡す 場合、
>予期せず "型の不一致" エラーが発生する可能性があります。

(マナ) 2022/01/08(土) 09:07


ご返信ありがとうございます!
本日下記の通り本に記載のまま書き直しをしてみました。
しかしながら、エラーが出ました。
(アドバイス頂きましたとおり、コードをそのままコピペしました。)

エラー部分は、「 Set pt = pc.CreatePivotTable(TableDestiation:=Range("A1"), TableName:="PV01")」の「TableDestiation」について指示され、
「名前付き引数が見つかりません」でした。

Sub 練習()

    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    For Each ws In Worksheets
        If ws.Name = "分析表" Then
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If
    Next

    Worksheets.Add(Before:=Worksheets("データX")).Name = "集計"
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("データX").Range("A1").CurrentRegion)
    Set pt = pc.CreatePivotTable(TableDestiation:=Range("A1"), TableName:="PV01")
    With pt
        .PivotFields("学年").Orientation = xlPageField
        .PivotFields("クラス").Orientation = xlColumnField
        .PivotFields("名前").Orientation = xlRowField
        .PivotFields("国語").Orientation = xlDataField
    End With
End Sub

(ジョー) 2022/01/08(土) 21:32


マナ様 ありがとうございます。
オブジェクトの範囲を指定する必要があるんですね!
毎回行数が変わるので、自動的に範囲を更新できるようにしたいのですが、コードをかけずにいます。
(ジョー) 2022/01/08(土) 21:58

タイプミスです。

Option Explicitをモジュールの1行目に記述するとよいです。
もし、タイプミスがあれば、教えてくれます。

また、タイプする際に、わざわざ大文字、小文字を使い分ける必要はありません。
すべて小文字で問題ありません。
タイプミスがなければ、ほとんどのケースで自動で大文字に変換してくれます。
変換されなければ、タイプミスを疑えばよいです。

(マナ) 2022/01/09(日) 08:53


>ほとんどのケースで

今回のケースでは、変換してくれないようです。

(マナ) 2022/01/09(日) 08:58


>モジュールの1行目に記述

モジュールというのは、コードを記述している場所です。
こんな感じにするという意味です。

 Option Explicit

 Sub 練習()
    Dim ws As Worksheet

(マナ) 2022/01/09(日) 09:05


ありがとうございます!
マナ様のおっしゃるとおり、記載エラーでした。
エラー修正し、
SourceData:=Worksheets(“デー
タ”).Range(“A1”).CurrentRegion.Address(external:=true)
としたら、無事ピボットテーブルが作成できました!!
ありがとうございます!

また 2022/01/08(土) 09:07 で教えて頂いた、
「名前付き範囲を設定して名前を文字列として渡す」については、
「Range(“A1”).CurrentRegion」ではなく、
まずはそのときのデータ範囲でのピボット作成を指示し、
追加で毎回データ範囲を取得して作成するように指示するような
コードを書くということでしょうか?

(ジョー) 2022/01/09(日) 21:25


 >まずはそのときのデータ範囲でのピボット作成を指示し、
 >追加で毎回データ範囲を取得して作成するように指示するような
 >コードを書くということでしょうか?

 違います。
 データ範囲を「元データ」と名前定義しておけば
 こんな感じにするという意味です。 

 SourceData:="元データ"

 あるいは、データ範囲をテーブル設定にして
 テーブル名が「テーブル1」であれば
 こうします、

 SourceData:="テーブル1"

(マナ) 2022/01/09(日) 22:15


マナ様

ありがとうございます!

>SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion

  ↓
  SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion.Address(external:=true)
で「Address(external:=true)」を付けることで
「SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion」
のセル範囲を文字列として返させているということでしょうか?

ちなみに毎日、前日の集計表を作ることを想定しております。
今回は元データは「データ」という名前の
シートのデータ(元データの数が変わる為、行数が毎日変わります。)で
ピボットテーブルを作成させたいです。
今のところ下記で動いていますが、データ範囲を名前定義する、
についてこの定義ができていないと言うことでしょうか?
Sub 練習()

    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    Worksheets.Add(Before:=Worksheets("データX")).Name = "集計"
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("データX").Range("A1").CurrentRegion Address(external:=true)))
    Set pt = pc.CreatePivotTable(TableDestination:=Range("A1"), TableName:="PV01")
    With pt
        .PivotFields("学年").Orientation = xlPageField
        .PivotFields("クラス").Orientation = xlColumnField
        .PivotFields("名前").Orientation = xlRowField
        .PivotFields("国語").Orientation = xlDataField
    End With
End Sub

(ジョー) 2022/01/10(月) 09:15


>で「Address(external:=true)」を付けることで
>「SourceData:=Worksheets(“データX”).Range(“A1”).CurrentRegion」
>のセル範囲を文字列として返させているということでしょうか?

はい、そうです。
従って、↓これの対策をしたことになります

>「型が違う」としてエラーが出てしまいます。

 '--------

>シートのデータ(元データの数が変わる為、行数が毎日変わります。)で
>ピボットテーブルを作成させたいです。

本を購入して勉強中の方に申し上げにくいのですが、
そもそもデータを更新するだけであれば
マクロを使はなくても可能です。ご存知でしょうか。

(マナ) 2022/01/10(月) 10:54


マナ様
ありがとうございます!
ピボットテーブルの場合、データ(行数が変わっても)は勝手に更新されるからわざわざVBAにしなくても良い、ということでしょうか?
ピボットテーブルが先に作成されている専用のブックを作って、
毎回新規データを貼り付けていけば良い、ということでしょうか??
(ジョー) 2022/01/11(火) 10:59

(続き)
貼り付ける&更新作業をすると何故かピボットテーブルのデータの順序が入れ替わってしまい、困っております。
(行@ABCその他→更新→その他CABといった感じです)
(ジョー) 2022/01/11(火) 11:16

可能であれば、昇降順の変更作業も無しで、データシートの列幅等勝手に調整→ピボットテーブル作成→結果を条件によって別シートに貼り付けるという作業を一括で出来るようにしたいと思うのですが‥
(ジョー) 2022/01/11(火) 11:19

そうですか。だとしても、すべてのステップをマクロに頼らなくても…と思いますが、

>シートのデータ(元データの数が変わる為、行数が毎日変わります。)で
>ピボットテーブルを作成させたいです

CurrentRegionでデータ範囲を求めているので、データ数が変わっても大丈夫です。

(マナ) 2022/01/11(火) 20:44


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.