'---- 특정 단어를 포함하는 시트만 취합하려면 입력하세요 -----
Const 포함시트명 As String = ""
'----------------------------------------------
 
Sub 파일및시트합치기()
 
Dim strFilePath As String: strFilePath = Multiple_FileDialog
run_Merge strFilePath
 
End Sub
 
'-----------------------------------------------
 
Sub run_Merge(paths)
 
Dim WB As Workbook
Dim WS As Worksheet: Dim toWS As Worksheet
Dim rng As Range
Dim i As Long: i = 0: Dim j As Long
Dim endCol As Long: Dim endRow As Long
Dim strWS As String
Dim varFilePaths As Variant: Dim varFilePath As Variant
 
'// 스크린업데이트 중단 (빠른 동작을 하려면 작은따옴표를 ScreenUpdating에 작은 따옴표를 제거하세요!)
'Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'// 오류방지
If Len(paths) = 0 Then
    MsgBox "병합할 파일을 선택하세요."
    Exit Sub
End If
 
'// 파일병합
Set toWS = ActiveSheet
j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row + 1
 
varFilePaths = Split(paths, ", ")
 
For Each varFilePath In varFilePaths
    Set WB = Application.Workbooks.Open(varFilePath)
    For Each WS In WB.Worksheets
        If WS.Name Like 포함시트명 & "*" Then
                With WS
                    endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                    endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                    Set rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
                    rng.Copy toWS.Cells(j, 1)
                    j = j + rng.Rows.Count
                End With
        End If
    Next
    WB.Close
Next
 
'// 안내메세지
MsgBox "파일 병합이 완료 되었습니다."
 
'//스크린 업데이트 활성화 (빠른 동작을 하려면 작은따옴표를 ScreenUpdating에 작은 따옴표를 제거하세요!)
'Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub
 
Public Function Multiple_FileDialog(Optional Title As String = "파일을 선택하세요", Optional FilterName As String = "엑셀파일", _
Optional FilterExt As String = "*.xls; *.xlsx; *.xlsm", Optional InitialFolder As String = "", _
Optional InitialView As MsoFileDialogView = msoFileDialogViewList, Optional MultiSelection As Boolean = True) As String
 
Dim FDG As FileDialog
Dim Selected As Integer: Dim i As Integer
Dim ReturnStr As String
 
Set FDG = Application.FileDialog(msoFileDialogFilePicker)
 
With FDG
    .Title = Title
    .Filters.Add FilterName, FilterExt
    .InitialView = InitialView
    .InitialFileName = InitialFolder
    .AllowMultiSelect = MultiSelection
    Selected = .Show
 
    If Selected = -1 Then
        For i = 1 To FDG.SelectedItems.Count - 1
            ReturnStr = ReturnStr & FDG.SelectedItems(i) & ", "
        Next i
        ReturnStr = ReturnStr & FDG.SelectedItems(.SelectedItems.Count)
 
        Multiple_FileDialog = ReturnStr
    ElseIf Selected = 0 Then
        MsgBox "선택된 파일이 없으므로 프로그램을 종료합니다."
        End
    End If
 
End With
 
End Function