シートを選んで内容を別なシートにコピーしたい

Question

今「結果.xlsm」でユーザーフォームを作成しております。
ユーザーフォームではリストボックスを２つ並べています。

一つ目のリストボックスでブックを選び、
二つ目のリストボックスでシートを選びます。
選んだシートの内容全てをコピーして
「結果.xlsm」の「データ」シートに貼り付けたいと思っています。

リストボックス１で
「氏名1」「氏名2」「氏名3」「氏名4」というブックを選択します。

リストボックス２で
それぞれのブックの中の「商品1～4」「商品11～17」「商品22～26」「商品33～40」からシートを選択します。

リストボックスを並べて商品名を選ぶところまではできたのですが、
そこから先に進めず困っておりました。

ここから先をどのように記述したら希望の動きになるか教えていただけませんでしょうか？
また、ブックやシートは固定ではなく増えたり減ったりします。
.AddItemで追加削除するのではなく、定義を追加する方法もあれば教えてください。

よろしくお願いいたします。

```ここに言語を入力
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "氏名1"
.AddItem "氏名2"
.AddItem "氏名3"
.AddItem "氏名4"
.ListIndex = 0
End With
End Sub

Private Sub ListBox1_Click()
With ListBox2
.Clear
Select Case ListBox1.List(ListBox1.ListIndex)
Case "氏名1"
.AddItem "商品1"
.AddItem "商品2"
.AddItem "商品3"
.AddItem "商品4"
Case "氏名2"
.AddItem "商品11"
.AddItem "商品12"
.AddItem "商品13"
.AddItem "商品14"
.AddItem "商品15"
.AddItem "商品16"
.AddItem "商品17"
Case "氏名3"
.AddItem "商品22"
.AddItem "商品23"
.AddItem "商品24"
.AddItem "商品25"
.AddItem "商品26"
Case "氏名4"
.AddItem "商品33"
.AddItem "商品34"
.AddItem "商品35"
.AddItem "商品36"
.AddItem "商品37"
.AddItem "商品38"
.AddItem "商品39"
.AddItem "商品40"
End Select
.ListIndex = 0
End With
End Sub
```

Accepted Answer

> 一つ目のリストボックスでブックを選び、
> 二つ目のリストボックスでシートを選びます。
> 選んだシートの内容全てをコピーして
> 「結果.xlsm」の「データ」シートに貼り付けたいと思っています。

セルの内容をコピーしたいと思った時、前提条件として

1）セル範囲の特定をする必要があります。
2）セルはシート上にありますので、どのシートに属するのかを特定する必要があります
3）そのシートは、ブックの中に存在しますので、どのブックのシートかを特定する必要があります。
4）セルをコピーするためにはブックを開いている必要があります。
5）ブックを開くにはそのファイルのフルパスを指定する必要があります。

ということは、
情報として保持しておきたいものは(表示は必要最低限でよい）、
1）ブック名(ファイルのベースネームでもよい）＆シート名
2）ファイルのフルパス
3）シート名
4）セル範囲（全てなので、UsedRangeプロパティで得られるセル範囲でよい？）

の、リストをまずは取得する必要があるでしょう。

そうすると、プログラムの流れとしては、

1）開きたいファイルはどのフォルダーにあるかを指定する
2）そのフォルダーの中のファイルのフルパスをリストにする（変数の中またはシート上に）
3）一覧から順次ファイルを開く
4）開いたファイル（＝ブック）の中のシート名をリストにする
5）開いたファイルを閉じる
6）3に戻る
7）取得したリストをリストボックスに入れる

というような流れになるかと（漏れがあるかも？）思います。
これらを一つづ解決していく必要があります。

----
そして、出来たリストボックスを選択することで、
1）フルパスとシート名などをを取得し、
2）指定のファイルを開く
3）指定のシートのセルをコピーする
4）ファイルを閉じる
（複数のブックにまたがる場合は、上記を繰り返す。）

というプログラムを起動したらいいと思います。

いずれもまだ出来てないという事なら、
マクロのあるブックと同じフォルダーから、
Excelファイルのフルパスの一覧をシート上に作ってみるところから始めるといいと思います。

その後は文字列の加工等いろいろなことを覚えてください。
シート上に一覧ができたら、それをリストボックスに転記してみてください。
（シート上にリストを作らなくてもいいけど、そうなると配列変数とかも勉強する必要が出てきます）

---
追記

> Excelファイルの一覧を作ることはできたのですが、
> その後が全くできません。
> せっかく詳しく教えていただいたのですが、
> これ以上できる気がしません。
> 申し訳ありません。

一朝一夕で出来るものではありません。
気長に根気強く勉強していくしか道はありません。
やりたいことと並行して、基礎をある程度やっておかないと、
メンテナンスまではこちらは面倒見きれません。
1つのプログラミング言語を覚えるのに、
最低200時間はかかると言われているようです。

以下をユーザーフォームのモジュールに書きます。

```ExcelVBA
Option Explicit

Private Sub UserForm_Initialize()
    Set一覧取得
    With Me.ListBox1
        .List = ThisWorkbook.Worksheets(1).Range("B1").CurrentRegion.Value
        .ColumnWidths = "100;0;0;0"
    End With
End Sub

Sub Set一覧取得()
    With ThisWorkbook.Worksheets(1).Cells(1)
        'シートの初期化
        .CurrentRegion.ClearContents

        'ファイルのフルパス一覧の取得
        Setファイルのプルパス一覧 .Cells

        'シート名の一覧取得
        Setシートの一覧 .CurrentRegion

        '作業列のクリア
        .EntireColumn.ClearContents
    End With
End Sub

Sub Setファイルのプルパス一覧(ByRef c As Range)
    Const csPath As String = "C:\Users\Public	est"
    Dim ixRow As Long
    Dim buf As String

    buf = Dir(csPath & "*.xlsx")
    Do Until Len(buf) = 0
        ixRow = ixRow + 1
        c(ixRow, "A").Value = csPath & buf
        buf = Dir()
    Loop
End Sub

Sub Setシートの一覧(ByRef rng As Range)
    Dim c As Range
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim ixRow As Long

    For Each c In rng
        Set wbk = Workbooks.Open(c.Value)
        For Each wsh In wbk.Worksheets
            ixRow = ixRow + 1
            With rng(ixRow, "B")
                .Offset(, 1).Value = wbk.FullName
                .Offset(, 2).Value = wsh.Name
                .Offset(, 3).Value = wsh.UsedRange.Address(False, False, xlA1, True)
                .Value = Get表示名(.Offset(, 3).Value)
            End With
        Next
        wbk.Close False
    Next
End Sub

Function Get表示名(ByVal sName As String) As String
    Dim i As Long
    
    i = InStr(sName, "'")
    If i > 0 Then
        Get表示名 = Left(sName, i - 1)
    End If
End Function
```

動きを目視で確認しやすいように、逐一シート上に展開してます。
実行して「出来た！」で終わらず、
ヘルプなりWebなりで調べて一行一行を理解されることを望みます。
↓を参考にしてステップ実行をしながら、シート上の結果と、
コードを見比べてみてください。
[プロパティ、メソッドの探り方 マクロ記録とＦ１のHelpを使う](http://www.ken3.org/vba/excel-help.html)

その他参考サイト
[Excel(エクセル)VBA入門：目次](http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/index.html)
[「VBA基本」のメニュー](http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html)
[Excel VBAコンテンツ](http://officetanaka.net/excel/vba/)
[エクセルマクロ・Excel VBAの使い方](https://www.relief.jp/Excel-VBA/)
[初めてのVBAマクロ "Hello World!"](http://home.att.ne.jp/zeta/gen/excel/c03p01.htm)

この辺で一通り網羅していると思いますが、
ExcelVBAの情報は多数ありますので検索してみて、
上手くいかなければ、別途質問してください。

＞＞Setファイルのプルパス一覧
プルパスってなんだ＾＾；
誤字です。
愛嬌ということで訂正しません＾＾；；
誰かに見られて恥ずかしければそちらで直してください。

Answer

まだ違うかもしれません。取り急ぎサンプルです。

メインのシートのユーザーフォームにボタンを配置します。
続いて、モジュールに下記を貼り付けます。

リストボックス1とリストボックス2からそれぞれ、BOOK名とsheet名を取得します。

そして転記関数を呼び出します。呼び出すBookは同一フォルダ内にあるものとします。

指定したブックを開いて、（処理を行い）閉じます。

処理ないようはご自身でお願いします。


```VBA

Sub 転記(book As String, sheetname As String)

Dim file As String
Dim wb1 As Workbook
Dim sh1 As Worksheet

file = ThisWorkbook.Path & "\" & book

'■bookを開く
 
Set wb1 = Workbooks.Open(file)
Set sh1 = workseeh(sheetname)


'■処理記述

msgbox "何か処理したよ！！"


'（例）データを転記する--ここはご自身で記述します。
//With ThisWorkbook.Sheets("data")
//    .Range("A2").value = wb1.sh1.Range("A2").value
//    .Range("B2").value = wb1.sh1.Range("A2").value
//
//End With

'■開いたbookを閉じる

wb1.Close savechanges:=Flase '(True)

Set wb1 = Nothing
Set sh1 = Nothing

End Sub


Private Sub CommandButton1_Click()
Dim book As String

Dim sheet As String

book = ListBox1.value & ".xlsx"
sheet = ListBox2.value

Call 転記(book, sheet)


コード
```

関連した質問