To combine multiple files (a certain sheet) into a workbook.

combiningMultiFiles.bas

Sub combiningMultiFiles()
	Dim wb As Workbook
	Dim fileName As String
	Dim combinedBook As Workbook
	Dim folderName As String
	Dim sheetNum As Integer
	Dim sheetNum As String
	Dim cnt As Long

	Application.ScreenUpdating = False

	Set wb = ThisWorkbook

	With wb.Worksheets(1)
		'フォルダ名の書かれたセルを指定する。設定シートのE6セルに入力。
		folderName = .Cells(6, 5).Value
		'まとめるファイルの種類を指定する。ここでは、.xlsx形式に限定。
		fileName = Dir(folderName & "\*.xlsx")
		'ワークブックの何枚目のシートをまとめるかを設定する。設定シートのE10セルに入力。
		sheetNum = .Cells(10, 5).Value
		'結合ファイルでのシート名を設定する。設定シートのE12セルに入力。
		sheetName = .Cells(12, 5).Value
	End With
	
	cnt = 1

	Do While fileName <> ""
		Workbooks.Open folderName & "\" & fileName

		If combinedBook Is Nothing Then
			Workbooks(fileName).Worksheets(sheetNum).Copy
			Set combinedBook = wb
		Else
			Workbooks(fileName).Worksheets(sheetNum).Copy before:=combinedBook.Worksheets(1)
		End If

		combinedBook.Worksheets(1).Name = sheetName & "-" & cnt

		Workbooks(fileName).Close False
		fileName = Dir()
		cnt = cnt + 1
	Loop

	Application.ScreenUpdating = True
	MsgBox "DONE"

End Sub