質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.51%

  • VBA

    2310questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 221

hiroro__7

score 16

前提・実現したいこと

掲題の件ですが、簡単に言うと、

  1. "Book1"の"Sheet1"の全データ範囲
  2. "Book2"の"Sheet1"のTableの"A1"
    上記1の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のシート1の全データ範囲を、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先で、インデックスがダメなようです。
3時間ほど色々試行錯誤しましたが、ググってもSheet間でのやりとりがほとんどで、
Bookの手順を見つけて同じようにしているつもりですが、どうしても無理だったので、
ご質問に至りました。

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

Excel 2016

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+1

これでできます。

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/02 08:57

    ありがとうございます。

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

    キャンセル

+1

期待する動作になったら問題ないけど、
日本語を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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/05 09:16

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

    キャンセル

+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 ワークシートの最終行、最終列を取得する

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/04/05 09:14

    ありがとうございます。

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

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

    キャンセル

同じタグがついた質問を見る

  • VBA

    2310questions

    VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。