Hello, I'm pretty new to this. Currently I have multiple Excel files with multiple sheets in them. To put simply, they are visual representations of BOM's for various machines. I currently place a copy in a folder and make a new sheet in that file for missing parts. In an effort to streamline parts ordering, I want to automate this process as much as I can. At the moment I open and close every sheet to check parts needed which gets a little tedious.
My end goal is to copy only the missing parts sheets to a master sheet to have everything needed in one place.
This is what I have hobbled together so far, it successfully copies the specific sheets I want into a master sheet. I can't seem to figure out how to rename the missing parts sheets to the original file name the missing parts sheet was pulled from.
Also is there any way I can run the macro and it update the sheet instead of making new sheets or check if a sheet is already there and only add new missing parts sheets if they are not in the master? Hopefully this is explained well enough.
My end goal is to copy only the missing parts sheets to a master sheet to have everything needed in one place.
This is what I have hobbled together so far, it successfully copies the specific sheets I want into a master sheet. I can't seem to figure out how to rename the missing parts sheets to the original file name the missing parts sheet was pulled from.
Also is there any way I can run the macro and it update the sheet instead of making new sheets or check if a sheet is already there and only add new missing parts sheets if they are not in the master? Hopefully this is explained well enough.
VBA Code:
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
'last sheet got an index equal to countSheets.
'the sheet before the last one will be then countSheets-1
If wksCurSheet.Name = "Missing Parts" Or wksCurSheet.Name = "Build 1 Missing Parts" Or wksCurSheet.Name = "Build 2 Missing Parts" Then 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