Merge Excel Files with the same Tabs names

[ljack]

Hello Guys,

I have a problem if you could help me out. I have the following vba code (found it, not created it):

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

As it's 'Sub' specifies it is a 'Excel Merger' and it works but it doesn't do what I really want.

Context: I am having 2 Excel Files with the same Tabs names and I need to merge them into the same Excel file but it won't let me because they have the same name. Also the first tab contains a pivot table that uses information from the other tabs.

What I need: I need to be able to merge them into one Excel file and also (maybe in a different VBA code) I would like to be able to copy only the values on the tab that contains the pivot table (simply removing any links and connections to other tabs, also it would be really nice to have this as an option that;s why it will be better to have it on a different VBA code).

Thank you so much,