Combine worksheets from multiple workbooks

[chlearning]

Good day experts, I'm attempting to combine one sheet out of multiple workbooks into the sheet where my module is located. The workbooks are located in the same file on the network, but I don't want all of them. The sheet name is the same in each workbook. I found a code that works well at pulling all sheets from the selected workbooks, but I only want the sheets named "BOM".

Also, this code drops the sheets into their own sheet in my "BOM Builder" workbook. I would like to combine these starting at B6 on sheet "BOM". I want the first heading (merged cells A1:F1, always the same location) in B6 and the cells (A5:E?) to start in the next row down. The number of rows varies. The next workbook "BOM" sheet copied would then skip a row, insert the heading and cells from that sheet, and so on.

If someone could tell me where/how to make it pull only the sheets I want, and then a separate sub to merge them the way I'm looking for, I would really appreciate it.

Here is the code that pulls the worksheets from the selected workbooks now.


Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

For Each fnameCurFile In fnameList
countFiles = countFiles + 1

Set wbkSrcBook = Workbooks.Open(fileName:=fnameCurFile)

For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If

Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.