VBA 他のブックにデータを自動転記する方法について

Question

お世話になっております。
連日の質問、大変申し訳ございません。
現在、VBAについての理解を深めるために様々なサイトを閲覧しているのですが、たまたま見かけたサイトで「閉じている他のブックにデータを自動転記する」（http://www.start-macro.com/55/w/l167.html ）方法で少々気になる点がありましたので、お力添えをお願いしたく思います。

該当のコード

Sub Macro1()
'
    a = ActiveCell.Value
    Range("A2").Select
    Workbooks.Open Filename:= _
        "C:\フォルダ名\ファイル名"
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = a
    Call Macro2
End Sub

Sub Macro2()
'
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

上記の"C:\フォルダ名\ファイル名"の部分なのですが、後から整理すべきファイルが増えた際、「該当のファイル名を追加する」というのは正直マクロを組んでいる意味がないと思っており、「1つのフォルダ内に格納されている全てのファイルの該当のシート」に対して動作するようなコードを書くことは可能なのでしょうか？

例えば
A━┳○○○1.xlsm
┣○○○2.xlsm
┣○○○3.xlsm
┣○○○4.xlsm
┣○○○5.xlsm
┗まとめ.xlsm

というフォルダ・ファイル構成となっていて、Excelファイルのsheet3に以下のような表があるとします。

番号	商品名
1	商品名1-1
2	商品名2-1
3	商品名3-1
4	商品名4-1
5	商品名5-1
6	商品名6-1

上記のようにファイル名が異なるものをまとめて制御して「まとめ.xlsm」に転記することは可能なのでしょうか？
また、「まとめ.xlsm」に既にデータが入力されている場合、最終行を取得して「○○○1.xlsm～○○○5.xlsm」のデータを順に転記していくことは可能なのでしょうか？
ファイル名が既に確定しているのであれば、各ファイルごとにCallすればよいとは思うのですが、確定しておらず増減する可能性がある場合はどのように考えるべきなのか…。

未熟者で大変申し訳ございませんが、皆様のお力をお貸しいただけますと幸いです。
何卒、宜しくお願い申し上げます。

Answer 1

お世話になります。
解決策のご提示並びにご返答が遅くなってしまい、大変申し訳ございませんでした。
諸事情により作業を進めることができていなかったのですが、
皆様からのご助言等をいただき、無事に目的を達成することができました。

以下にコードを記載し解決とさせていただきます。
誠にありがとうございました。

Private Sub CommandButton1_Click()

'作業データ取込  '選択したファイルを取り込み、別のファイルに貼り付ける。

    Dim RC As Integer
    Dim OpenFileName, fileName, Path, SetFile As String
    Dim wbMoto, wbSaki As Workbook

    Set wbMoto = ActiveWorkbook  '全ての一覧の取り込み元をセット

    Application.DisplayAlerts = False
    
    RC = MsgBox("リスト一覧を読み込みますか？", vbYesNo + vbQuestion, "確認")
    
    If RC = vbYes Then
            
            OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
            'ダイアログボックスを表示して、取り込みたいリスト一覧のファイルを指定します。
            
            If OpenFileName <> "False" Then
                 SetFile = OpenFileName
            Else
                MsgBox "キャンセルされました"
                Exit Sub  'リスト一覧の取り込みをキャンセル
            End If
    
            
            Workbooks.Open fileName:=SetFile, ReadOnly:=True, UpdateLinks:=0
            'ダイアログボックスで指定した取り込みたいリスト一覧のファイルを開きます。
            
            Set wbSaki = Workbooks.Open(Path & SetFile)
            
                'ワークブック間のをコピーします。
                LstRow1 = wbSaki.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
                wbSaki.Worksheets("Sheet1").Range("A2:D" & LstRow1).Copy
                
                LstRow2 = wbMoto.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
                wbMoto.Worksheets("Sheet1").Range("A" & LstRow2).PasteSpecial xlPasteFormulasAndNumberFormats
                
                Application.CutCopyMode = False  'コピー切り取りを解除
                wbSaki.Close False  '取り込みたいリスト一覧のファイルを閉じる
                
                
        Else
        
        MsgBox "処理を中断します"
    
    End If
    
    Application.DisplayAlerts = True


End Sub


コード

Answer 2

フォルダ内のファイル名を取得する部分は理解されているとのことなので、残りの部分について

１．「まとめ.xlsm」の貼り付け先の最終行＋１行を求める
２．コピー元のファイルを開く
３．コピー元ファイルのコピー元シートのコピーデータをコピー
４．「まとめ.xlsm」へ貼り付け

１．～４．をファイル数分繰り返す

で可能かと思います。

Answer 3

例えば「vba フォルダ内のファイル名を取得」というキーワードでWEB検索すればサンプルコードは見つかります。
例えば、
Office TANAKA - ファイルの操作[ファイルの一覧を取得する]

Comments

[shibakoppe]

度々ご迷惑をおかけし申し訳ございません。
フォルダ内のファイル名を取得する手段は理解しているのですが、1つのまとめファイルに対して、増加していくファイルのデータを最終行に追加していく手段が、いくら調べても思いつきません…。
他にも参考にすべきサイト等がありましたらご教授いただけますと幸いです。

[hatena19]

最終行の取得法がわからないのであれば「VBA 最終行を取得」で検索すればいいでしょう。

最終行の取得（End,Rows.Count）｜VBA入門
https://excel-ubara.com/excelvba1/EXCELVBA318.html

データの追加はコピー&ペーストでいいでしょう。

VBA セルをコピーする (Range.Copy)
https://www.tipsfound.com/vba/07025