Excelのマクロでピポットキャッシュを作成しようとすると、実行時エラー13『型が一致しない』とエラーになる。

Question

現在、ExcelのVBAで自動的にピポットテーブルを作成したいと考えています。
元データとなるSheets1の行数及び列数が毎回変動することから、
以下の様な記述にしたのですが、ピポットテーブルを作成するための
キャッシュでエラーが発生しています。

<記載内容>
Sub TEST()

'-----型宣言-------'
    Dim acWb As Workbook     'acWbという関数はワークブックであると宣言。
    Dim LastRow As Long      '列の最大値を求めるための変数宣言（列数）
    Dim LastColumns As Long  '行の最大値を求めるための変数宣言（行数）
    Dim MaxCells As String   '最大値アドレス
    Dim PCache As PivotCache 'ピポット作成時のキャッシュ置き場
   

    Set acWb = ThisWorkbook  'acWbにこのワークブックをセット
 
'-----行の取得-------'
        LastRow = acWb.Worksheets("sheets1").Cells(Rows.Count, 1).End(xlUp).Row
                    
'-----列の取得-------'
         LastColumns = acWb.Worksheets("sheets1").Cells(1, Columns.Count).End(xlToLeft).Column

'-----範囲計算-------'
        MaxCells = acWb.Worksheets("sheets1").Cells(LastRow, LastColumns).Address
               
'-----データシートからピポット用のキャッシュを作成-------'
        Set PCache = acWb.PivotCaches.Create( _
              SourceType:=xlDatabase, _
              SourceData:=acWb.Worksheets("sheets1").Range(Range("A1"), MaxCells))
             
End Sub

<エラーの内容>
　実行時エラー13
　型が一致しません。

<エラー箇所>
        Set PCache = acWb.PivotCaches.Create( _
              SourceType:=xlDatabase, _
              SourceData:=acWb.Worksheets("sheets1").Range(Range("A1"), MaxCells))

<テストに使用しているSheets1の内容>
　列　　　　→　A～FQ列
　行　　　　→　1～57449行
　データ個数→　4711423個

<これまで確認したこと>
　エラー箇所に記載した、Range(Range("A1"), MaxCells))　が要因というところまで
　つかめたのですが、回避方法を見つけることができないことから、投稿させて頂きました。
　
　該当箇所を変数ではなくtestyrange("A1,FQ57449")を入力することで動作するのですが、
　毎実行ごとに、MaxCellsの値が変わることから、何か良いアドバイスいただけますと幸いです。

Answer 1

hornet1119さん、こんにちは。

取り敢えずそれらしい適当な表をRange("A1")からRange("D12")までに作って、ご呈示のコードを実行(Windows10+Excel2016)してみましたが、何もエラーが出ませんでした。

ご呈示のような　A~FQ列　×　１～57449行　データ個数4711423個の表となると、ちょっと検証分析が難しそうです。

取り敢えず、
SourceData:=acWb.Worksheets("sheets1").Range(Range("A1"), MaxCells))
の「Range("A1")」を文字列型のアドレスであるMaxCellsに合わせて「"$A$1"」に変更してみてはいかがでしょうか。Range("A1")ではオブジェクト(シート)が特定されていないので、sheets1でないシートがアクティブであるとエラーになります。

Answer 2

hornet1119さん、こんにちは。

A~FQ列、１～57449行、データ個数は一致しませんが、適当な表を作成して、ご呈示のコードを実行しましたが、やはり、エラーは出ませんでした。なぜでしょう？不思議です。

エラーが再現確認できませんので、解決法になるとも思えないものの、前報で記しましたが、Range("A1")を　”＄A1$1"　又は　acWb.Worksheets("sheets1").Rnage("A1")　に書き換えてみることをお勧めします。

Answer 3

気になる点を２点ほど。

> MaxCells = acWb.Worksheets("sheets1").Cells(LastRow, LastColumns).Address

.Address はいるのですか？
下記のようにするべきでは？

Set MaxCells = acWb.Worksheets("sheets1").Cells(LastRow, LastColumns)


> SourceData:=acWb.Worksheets("sheets1").Range(Range("A1"), MaxCells))

sheets1 はアクティブになっていますか？
アクティブであることが確実でないなら、下記のように書く必要があると思います。
acWb.Worksheets("sheets1").Range(acWb.Worksheets("sheets1").Range("A1"), MaxCells))

※パッと見の所感であり、SourceDataに与える型がrange型なのか文字列型（＝セルアドレス）なのかは確認していないので、この点はご自身で確認してくださいね。

Answer 4

minmin312さん、こんにちは。横から失礼します。

>下記のようにするべきでは？
>Set MaxCells = acWb.Worksheets("sheets1").Cells(LastRow, LastColumns)

hornet1119さんは次のように定義されていますので、minmin312さんのご指摘の様にする場合は定義も変更しないと駄目なのではないでしょうか。
>Dim MaxCells As String   '最大値アドレス
言うまでもないことなので、説明を省略されたのだろうとは思いますが、念のため。

なお、hornet1119さんの提示された元のコード(MaxCells=アドレスの文字変数)のままでも、問題は無いように思います。ただし、Range("A1")についてのご指摘(シートオブジェクトを特定すべきこと。)は、その通りだと思います。

Answer 5

KokemomoYamamomoさん

> hornet1119さんの提示された元のコード(MaxCells=アドレスの文字変数)のままでも、

> 問題は無いように思います。

Rangeオブジェクトでしか指定できないものと勘違いしていましたが、どちらでも良いのですね。

勉強になりました。ありがとうございます。