【VBA】Sheet1から他のBookのSheet1へCopyする方法がわからないです????

Question

前提・実現したいこと

掲題の件ですが、簡単に言うと、
0. "Book1"の"Sheet1"の全データ範囲
0. "Book2"の"Sheet1"のTableの"A1"
上記１のBook1の全データ範囲をCopyして、Book2のDTの"A1"から同じ範囲に値のみ、貼り付けを行いたい。です。

上記ブック間のCopy＆Paste機能を実装中に以下のエラーメッセージが発生しました。
インデックスが有効範囲にありません。

発生している問題・エラーメッセージ

エラーが有効範囲にありません

該当のソースコード

Option Explicit
Dim MaxRow As Long
Dim MaxColumn As Integer

Public Sub CopySheet1_PasteMaster()
Dim stTestFile As String
stTestFile = "test.xlsx"
Range("A1").Select
MaxRow = Range("A1").SpecialCells(xlLastCell).Row
MaxColumn = Range("A1").SpecialCells(xlLastCell).Column
    '#####################################
    ' Book.xlsxのシート１の全データ範囲を、stTestFileのSheet1のA1へ貼り付ける処理
    '#####################################
    Workbooks.Open Filename:=stTestFile　　' ファイルを開く
　　 ' データ範囲をコピーし、Tableが設定済みのSheet1のA1セルを起点として値のみ張り付けを行う。
    Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, MaxColumn)).Copy _
       Destination:=Workbooks(stTestFile).Worksheets("Sheet1").Range("A1", Cells(MaxRow, MaxColumn))
 Range("A1").Select
End Sub

試したこと

Destination:=Workbooks(stTestFile).Worksheets("Sheet1").Range("A1", Cells(MaxRow, MaxColumn))

上記コード、Paste先で、インデックスがダメなようです。
３時間ほど色々試行錯誤しましたが、ググってもSheet間でのやりとりがほとんどで、
Bookの手順を見つけて同じようにしているつもりですが、どうしても無理だったので、
ご質問に至りました。

補足情報（FW/ツールのバージョンなど）

Excel ２０１６

Answer 1

解決済みですが、ご参考までに。

まず、エラーの原因ですが、おそらく下記のコードが原因でしょう。

Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, MaxColumn)).Copy _

この前で、貼付先ブックをOpenしてますので貼付先ブックがアクティブになってます。
Cells(MaxRow, MaxColumn)はアクティブなブックのシートのセルになります。
別のシートのセルをRangeの引数にしているのでエラーになります。

ここは、
Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, Workbooks("Book1.xlsx").ActiveSheet.MaxColumn)).Copy _
というようにどのシートのセルかを省略せずに記述する必要があります。

このようなミスを避けるためにも、下記の方針にするといいでしょう。

• SelectやActivateはしない

(しなくてもコピーはもちろん、大抵の操作はできます。また、Selectすると遅くなります。)

• Cells() や Range() というようにブックやシートの参照を省略しない。

省略しないと上のコードのように長くなりますので、参照するブックやシートは変数に代入して使用するか、Withステートメントを使用する。

上記の方針でコーディングすると下記のようになります。

Public Sub CopySheet1_PasteMaster()
    Dim stTestFile As String
    stTestFile = "C\test\test.xlsx" 

    'コピー元セル範囲
    Dim FromRng As Range
    With ThisWorkbook.Worksheets("Sheet1")
        Set FromRng = .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
    End With

    '貼付先ブック
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:=stTestFile)

    '貼付先セル範囲
    Dim ToRange As Range
    Set ToRange = wb.Worksheets("Sheet1").Cells(1, 1).Resize(FromRng.Rows.Count, FromRng.Columns.Count)
    '貼付先がテーブルなら下記のコードで
    'Set ToRange = wb.Worksheets("Sheet1").ListObjects(1).DataBodyRange.Resize(FromRng.Rows.Count, FromRng.Columns.Count)

    FromRng.Copy Destination:=ToRange '書式もコピーされる
    'ToRange.Value = FromRng.Value  '値だけの場合はこちらのコードで
    
End Sub

どうでしょうか。
シンプルだし、コピー元、貼付先が明確で読みやすいコードになっていると思いませんか。

---

ちなみに、

.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))
と
UsedRange
は微妙に異なります。(下記リンク参照)。どちらが質問者さんのご希望にそっているか分かりませんので、その辺は違いを認識して適切な方を選択してください。

No.8 ワークシートの最終行、最終列を取得する

Comments

[rara]

ありがとうございます。

実はオブジェクト型の操作は少し前はまだ知らなくて、
つい先日勉強して、習得しました。

ご提示いただきましたコードも今なら読めるように。
すごく見やすくなっていますね。
このようなコードが書けるよう、頑張りたいと思います。

Answer 2

期待する動作になったら問題ないけど、
日本語をVBA語に直したらこんな感じじゃないかな。。。

Sub test()
    'Book1ブックのSheet1シートの全データ範囲をCopyして、
    Workbooks("Book1.xlsx").Worksheets("Sheet1").UsedRange.Copy
    'Book2ブックのSheet1シートのDTの"A1"から同じ範囲に値のみ、貼り付ける。
    Workbooks("Book2.xlsx").Worksheets("Sheet1").ListObjects(1).Range.Range("A1").PasteSpecial Paste:=xlPasteValues
End Sub

期待する動作になるかは試してないので不安ですが＾＾；

Comments

[rara]

日本語をVBAにしていただいて、ありがとうございます。
今回は他の方のを参考にいたしましたが、またよろしくお願いいたします。

Answer 3

これでできます。

copyBook = ThisWorkbooks.Name
stTestFile = "test.xlsx"

Workbooks.Open Filename:=stTestFile

Workbooks(copyBook).Activate
Sheets(1).Copy _
Before:=Workbooks(stTestFile).Sheets(1)

また、現在使用中のデータ範囲のみ(例えば、Range("C2:F30"))を別ブックのRange("A1")から貼り付ける場合は下記でもできます。

copyBook = ThisWorkbooks.Name
stTestFile = "test.xlsx"

Workbooks.Open Filename:=stTestFile

copyBook.Activate
copyBook.UsedRange.Copy

Workbooks(stTestFile).Activate
Range("A1").PasteSpecial

Comments

[rara]

ありがとうございます。

早速試して、うまくCopyができました！！