Combining 3 excel files into 1 workbook using VBA

[LarissaEng]

I have 3 excel files that I am trying to combine into one file so that they can be sorted in a particular tab order. I have the below code that I am trying to use that merges the files together. The issue I am having is that it is linking the cells back to the original file and I just want the files to copy over. If I have blank cells in it is adding zeros on the new file. Is there a way that I can copy without changing any of the formating or data? These tabs are graphs and spreadsheets. I am new to VBA and trying to figure this out. I have the coding for the sorting of the tabs. In the end this will have about 135 tabs.

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 "Procesed " & 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

 

[WassimN]

Hi LarissaEng

I used the recorder to get these two example lines for you:

'Copy a worksheet
Sheets("Sheet1").Copy Before:=Workbooks("Book1").Sheets(1)
'/Move a worksheet
Sheets("Sheet1").Move Before:=Workbooks("Book1").Sheets(1)

You may want to look into the cases where there is a worksheet with a name of the worksheet you are trying to move. And other things like that.

I use the recorder for a lot of things and then tailor the code generated to my needs. I think it is a great idea