元ブックへ他ブックから指定したシートをそっくりそのままコピーしたい

Question

題名通りです。

イメージとしては元ブックマクロを起動するとファイル選択ウィンドウが表示され、そこで別ブック、そしてそのシート名を指定。
そしてその別ブックの指定したシート内容をそのまま元ブックのシート2へコピーしたいというものです。

ファイル選択などはApplication.GetOpenFilenameあたりでできるのではないかと思いましたが、シートの全コピー・ペーストのやり方がよくわからず困っております。
どなたか方法などの流れをご教授ください。

Answer 1

質問の内容を

シートの全コピー・ペーストのやり方

だと理解しています。
コメントなさったエラーはどちらかブックが開けてないということだろうと思います。
こう書き下して、ブレークポイントを置いていけば、どっちが開けてないか分かるはずです。

Sub CopyTo()
  Dim src As Worksheet
  Set src = Workbooks("Book2.xlsx").Worksheets("Sheet1")
  Dim dst As Worksheet
  Set dst = Workbooks("Book1.xlsm").Worksheets("Sheet1")
  src.Copy After:=dst
End Sub

とりあえずは、手動で、二つのファイルを開いてコピーできることをご確認ください。

ファイルダイアログからブックを開くことができないというのは、質問の粒度として、別の(前段階の)質問です。
仮に全部が聞きたいなら、コピーのしかたではなく開いてコピーする方法、という質問にしてください。
概略としてはこういう感じだと思います。

OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If OpenFileName <> "False" Then
   Set wb = Workbooks.Open OpenFileName
EndIf
Set src = wb.Worksheets("Sheet1")

Comments

[k207067]

FileName = Application.GetOpenFilename( _
filefilter:="Excelファイル,*.xls*", _
MultiSelect:=True)
Workbooks("FileName").Worksheets("コピー元シート").Copy After:=Workbooks("別データ.xlsm").Worksheets("Sheet3")

のような感じでやってみたのですが、やはりパスだけではインデックスエラーになりますよね・・・

[papinianus]

「やはりパスだけでは」:おっしゃる意図が分かりません
「インデックスエラー」:開いてないですからね、多分。開いてください。

[shinobu_osaka]

なぜこの程度の質問が解決済みになってないのかと思って見に来たら何言ってるのかわからなかった…。 そして Workbooks("FileName") のダブルクウォートいらないでしょ…。　ブレークポイントの使い方覚えて少しづつ動かしながらちゃんと動いてるか確認してどこでコケてるのか調べるべきかと。

Answer 2

やりたいことは
＞元ブックマクロを起動するとファイル選択ウィンドウが表示され
＞別ブック、そしてそのシート名を指定
＞指定したシート内容をそのまま元ブックのシート2へコピー
ですよね。

つまり、
・対象ファイルの選択
・対象シートの選択
・選択したシートのコピー
の３つの作業を行いたいのだと思います。

この作るべきものをぼやけたイメージのまま進めてしまうと、
＞ファイル選択「など」はApplication.GetOpenFilename「あたりで」
といった具合に作るべきものもぼやけたイメージになってしまいます。

さしあたり、現時点の質問内容にはシート選択の方法について具体的な内容が見当たりません。
シートはどのように選択させる想定でしょうか？
（ユーザーフォームにリストボックスを配置し、シート名を一覧表示させたうえでダブルクリックで選択させる、等）

ここらへんはモノ作りを始める前にしっかりイメージしておきましょう。
それによって処理の実装方法などもかわってきます。

以下、papiniausさんへのコメントで記載されたコードを元にしたアドバイスです。

①対象ファイルの選択

まず
Workbooks("FileName").Worksheets("コピー元シート").Copy After:=Workbooks("別データ.xlsm").Worksheets("Sheet3")
の部分ですが、shinobu_osakaさんからも指摘がある通りWorkbooks("FileName")では"FileName"という名前のブックを探してしまいます。

しかもそのファイル名はファイル選択ダイアログで指定しただけの段階で、まだ開かれてもいません。
Workbooks(ファイル名)では開いているブックから対象ブックを探しますので、その前にOpenしてあげる必要があります。

加えてもう１点、
FileName = Application.GetOpenFilename(filefilter:="Excelファイル,*.xls*", MultiSelect:=True)
の部分ですが、ファイル選択ダイアログを表示する際に敢えて複数ファイル選択できるように指定しています。

この場合、変数FileNameには選択した１つまたは複数のファイル名が文字列配列として格納されます。
ファイル名を取り出す際にはFileName(0)のように添え字が必要になりますし、複数指定されても次の対象シート選択でどのように選択させるのか困ってしまいそうです。
ここでは単一ファイル選択(MultiSelect:=False)を指定したほうがよいのではないでしょうか。

'①-1：ファイルの選択
Dim FileName As String
FileName = Application.GetOpenFilename(filefilter:="Excelファイル,*.xls*", MultiSelect:=False)

'①-2：対象ブックを開く
Dim wb As Workbook
Set wb = Workbooks.Open(FileName)

・ファイルは単一選択
・次の段階で「ブックに含まれるシート名の一覧」必要になるので、ここでブックをオープンしておく。

②対象シートの選択

①でオープンしたブックからシート一覧を取得します。
これはWorkBook.WorkSheetsに格納されているすべてのシートをループ処理して、シート名を取得していけばいいと思います。

取得したシート名をどのように選択させるかは前述のとおり不明なのですが、例えばリストボックスを配置したユーザーフォームを用意しそこに一覧表示する場合、以下のようなコードで実現できます。

'②-1：シート名一覧の取得
Dim sh As Worksheet
'全てのシートをループ処理
For Each sh In wb.Worksheets
    'シート名をリストに追加
    UserForm1.Listbox1.AddItem sh.Name
Next

③選択したシートのコピー

ここまでくれば、シートのコピーは既存のコードを少し変更するだけで実現できると思います。
wb.Worksheets(UserForm1.Listbox1.Value).Copy After:=Workbooks("別データ.xlsm").Worksheets("Sheet3")

開いたブックから選択したシート名を指定してコピーしています。

まとめると

個々の処理については以上です。

説明の中で、シート選択方法の例としてユーザーフォームを用意する方法をご紹介しました。
このやり方だと、メインマクロ側で開いた対象ブックの上方やフォーム側で選択したシート名などの情報を、メインマクロとフォームマクロの間でお互いに受け渡してあげなければならなくなります。

こういったやりとりが面倒であれば、①からのすべての処理をフォーム側のマクロとして実装してしまう方が楽かもしれません。

Private wb As Workbook
Private FileName As String

'フォーム起動時
Private Sub UserForm_Initialize()
    '①-1：ファイルの選択
    FileName = Application.GetOpenFilename(filefilter:="Excelファイル,*.xls*", MultiSelect:=False)
    
    '①-2：対象ブックを開く
    Set wb = Workbooks.Open(FileName)
    
    '②-1：シート名一覧の取得
    Dim sh As Worksheet
    '全てのシートをループ処理
    For Each sh In wb.Worksheets
        'シート名をリストボックスに追加
        ListBox1.AddItem sh.Name
    Next

End Sub

'リストダブルクリック時
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    '選択したシートをコピーする
    wb.Worksheets(UserForm1.ListBox1.Value).Copy After:=ThisWorkbook.Worksheets("Sheet2")
    
    '対象ブックを閉じる
    wb.Close
    Set wb = Nothing
    
    'フォームを閉じる
    Unload Me
End Sub

以上、長くなりましたが参考になれば幸いです。

Comments

[k207067]

分かりやすい説明ありがとうございます。
サンプルを参考にもう一度流れを理解してみようと思います。

Answer 3

イメージとしては元ブックマクロを起動するとファイル選択ウィンドウが表示され、そこで別ブック、そしてそのシート名を指定。
そしてその別ブックの指定したシート内容をそのまま元ブックのシート2へコピーしたいというものです。

よく考えてみてください。
手動でやる手間と全く同じことを、わざわざ、VBAで新たにしようとしています。
それでも、どうしても自分ごのみの表示＆選択でやりたいということですか？

ファイル選択などはApplication.GetOpenFilenameあたりでできるのではないかと思いましたが、

GetOpenFilenameメソッドは、ファイルを開くダイアログボックスが表示されますが、実際にはファイル名の取得のみで、実際には開いてくれません。
なので、まず名前を取得して、それから
Wrokbooks.Openメソッドで別途開いてやらなければなりません。

これらの詳しい情報はその命令をネットで検索すれば詳しく解説しているサイトが見つかるはずです。
いつ回答がつくかわからない掲示板で質問するより、
検索が上手になった方が解決が早いと思いますよ。

まぁ、複数ブックをいっぺんに選択して（というかフォルダを選択してその中のExcelファイル全部の）、すべてのシート名を一覧で表示させるのはぼくも作ったことがあります。
マウスのクリック回数を減らすのは意義があると思いますが、
説明を読む限りマクロの必要性を感じません。