最終行、最終列の取得がうまくできません

Question

ピボットテーブルよりデータを取得したいのですが
開始セルが"A5"セル、最終セルが毎度可変するため最終行と列を変数で指定しています。
以下コードを実行した際に「※」の行で「アプリケーション定義またはオブジェクト定義のエラーです」のエラーが出てしまいます。

なぜか、lrとlcを入れ替えるとエラーにはならず実行できます。
また選択範囲も入れ替えたセル番地を最終とみなし選択できます。
どなたか原因わかる方、ご教授お願いします。。。

        Dim lr, lc As String
        dim S1,D1 As Range
            Worksheets(2).Select
            
            lc = Cells(5, Columns.Count).End(xlToLeft).Column
            lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
            
            ※S1 = Range("A5", Cells(lr, lc)).Copy
            Set D1 = bulk2.Worksheets(1).Range("A2")
            D1.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Answer 1

質問の答えとしては、lr と lc が文字列型になっているのが原因です。
数値型に変えてやって下さい。

以下、蛇足。
Cells って左側は文字列でも通るんですね、知りませんでした。
あえて使う必要はなさそうですが。
（※追記：
右側も、文字列でも存在する列(A・AB・IV 等)なら通るようです。
内容的には同様なので以下の文章も読み替えてください）

Sub Cells_Test()
    Cells(8, 13).Select   '通る
    Cells("8", 13).Select '通る
    Cells(8, "13").Select '通らない
End Sub

上を踏まえて逆にすると通る理由は
Dim lr, lc As String
↑のように宣言をした時、
lc は文字列型として宣言されますが、
lr はVariant型(数値も文字列もＯＫ)で宣言されるのが原因です。
参考URL

数値もＯＫのlrがCellsの右側の値になるので逆にすれば(ギリギリ)通ります。

Comments

[hatena19]

Cellsは、数値で行列のインデックスを指定する以外に、
文字列で "A1" 形式で指定することもできます。
A5セルなら、
Cells("5","A")
というように。
行は、数値でも文字列でも同じになりますので、たまたま通るということですね。

[torisan]

Cells(8, "M").Select で試した所通りました。
私の例なら、文字列だからではなく
『13という列は無い』という事ですね、納得しました。

[4n5]

ご回答ありがとうございます。
まず型が違うかったのですね。
まだまだ初歩的なミスが多いのでしっかりと基本を勉強していきたいと思います。
string→longに変えて無事完了できました。
ありがとうございました！

Answer 2

蛇足かもしれませんが、
対象シートの使っているセル範囲は、UsedRangeで取得できるから、
その中で不要な部分を取り除いてやるという考え方もできますよ＾＾

Sub サンプル()
    With ThisWorkbook.Sheets(1).UsedRange
        Intersect(.Cells, .Offset(5)).Copy
    End With

    Workbooks("book2.xlsx").Sheets(2).Range("A2") _
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

あと、提示のコードでいうと、

Worksheets(2).Select

2つブックを開いていると、どっちのブックがアクティブなのかで、
操作対象が変わってしまうので、2つのブックを扱うなら、
ちゃんとブックから明示しましょう。
あと、Selectしなくてもコードで指定したら操作できるので、
Selectしなくていい書き方を覚えましょう。

Sub さんぷる2()
    Dim wsOld As Worksheet
    Dim wsNew As Worksheet
    Dim r As Long
    Dim c As Long
    
    Set wsOld = ThisWorkbook.Worksheets(2)
    Set wsNew = Workbooks("Book2.xlsx").Worksheets(1)
    r = wsOld.Cells(5, wsOld.Columns.Count).End(xlToLeft).Column
    c = wsOld.Cells(wsOld.Rows.Count, "A").End(xlUp).Row - 1
    
    Application.Range(wsOld.Cells(5, "A"), wsOld.Cells(r, c)).Copy
    wsNew.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

ああああ？

ピボットテーブルよりデータを取得したいのですが

コピー元がピボットテーブルなのですか？
それなら、ピボットテーブルの情報からセル範囲を得る方がスマートかも？
コードはサンプルデータがないので、探るのがめんどうなので、
情報が得られてからかな。。。

Comments

[4n5]

ご回答ありがとうございます。
＞UsedRangeで取得できるから、その中で不要な部分を取り除いてやる
最初はこちらも考えたのですが、イマイチどのように組立すれば分からなかったので現在の仕様になりました。
とても短いコードでできるんですね。。。
参考にさせていただきます。
また、他のご指摘もありがとうございます。

＞ピボットテーブルの情報からセル範囲を得る
こちらはピボットのデータソースということでしょうか？

[mattuwan]

>ピボットテーブルよりデータを取得したいのですが

の意味がよくわかってないです。
ピボットテーブルをさらに自分流に加工したいのかなとは、
想像しているのですが。。。

Answer 3

すでに回答がでていますが、 lc変数が文字列になっているのが原因ですね。

ただ、他にも間違いがあるので指摘しておきます。

Dim lr, lc As String

これは、Stringとして宣言しているのはlcのみになります。
lr は型指定してないことになり、Variant型となります。
VBAでは面倒でも、ひとつずつ型指定する必要があります。今回は、

Dim lr As Long, lc As Long

とすべきですね。

S1 = Range("A5", Cells(lr, lc)).Copy

は、戻り値は必要ないので、

Range("A5", Cells(lr, lc)).Copy

とするだけでいいですね。

追記

最終行、最終列の取得に関しては下記に纏められてます。
No.8 ワークシートの最終行、最終列を取得する
いろいろな方法がありますが、それぞれ微妙に異なりますので留意しておくといいでしょう。

今回は、ピボットテーブルということなので、空の列や空の行はないと思われますので、CurrentRegionを使うのが一番シンプルかと思います。

Worksheets(2).Range("A5").CurrentRegion.Copy
bulk2.Worksheets(1).Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

あるいは、PivotTablesを使うのも確実ですね。

Worksheets(2).PivotTables("ピボットテーブル1").TableRange1.Copy
bulk2.Worksheets(1).Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Comments

[4n5]

ご回答ありがとうございます。
分かりやすくご指摘いただき大変助かりました。
複数の宣言もVariant型以外はひとつひとつ宣言しなければいけないのですね。。。
また一つ勉強になりました。
ありがとうございました！

[4n5]

皆様の心強いお力添えのおかげで思い通りに動かすことができました！
自分からすれば皆様の回答を全てベストアンサーとしたいのですが、今回はプラオンで色々とご指摘いただいたhatena19様のアンサーをベストアンサーとさせていただきます。
CurrentRegionやPivotTablesも試してみたいと思います。