Macro for Merging Excel Files in One Workbook with Overwriting

[njpamdnc]

Mr. Excel...My name is Robert, and I am a middle school math teacher who wants to merge multiple Excel files into a single workbook. Furthermore, I need the macro to replace/overwrite current files/worksheet tabs with new files/worksheet tabs that have the same name. I have read several similar posts in the message forum. However, I couldn't find information regarding replacing/overwriting. The following macro works terrific with the exception of not replacing/overwriting old data with new data:

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

 

[njpamdnc]

I want you to help me clear up some confusion about the following information:


I was actually just trying to make sure if you have all SIAR(XX) worksheets already in the main workbook. Since you have SIARQX worksheets that are supposed to be referring to the SIAR(XX) worksheets with formulas, I believe you should have all SIAR(XX) worksheets in the main workbook.

Could you please try the following macro. Instead of delete/copy worksheets, this one transfer the "values" of the used range of the worksheets. Although I believe all SIAR(XX) worksheets already exist in the main workbook, I still set the macro to transfer if a SIAR(XX) is missing in the main workbook.


All 45 SIAR(XX) worksheets were created in the main workbook immediately after I created the 4 SIARQX worksheets. If I didn't already have the SIAR(XX) worksheets located in the main workbook, there would have been nothing to replace during the new file import process. However, one could argue new files would still be imported into the main workbook regardless of the existence of current(old) files.

 

[njpamdnc]

Your second macro modification preserved the formula links. Therefore, Problem #2 has been fixed as you previously stated. Is it safe to assume all I have to do for the other 3 SIARQX tabs is copy/paste the formulas from SIARQ3?

 

[smozgur]

Your second macro modification preserved the formula links. Therefore, Problem #2 has been fixed as you previously stated. Is it safe to assume all I have to do for the other 3 SIARQX tabs is copy/paste the formulas from SIARQ3?

The macros I provided have nothing to do with creating formulas in SIARQX worksheets.

However, if you copy formulas from one worksheet to another, then they will basically refer to the same cells. Therefore, I don't quite understand what you mean by "safe". I might be wrong but it sounds like you are going to have the same content in all SIARQX worksheets if you copy/paste formulas from SIARQ3 to the others. Perhaps you have other distinct content in each SIARQX page that formulas are referring to retrieve data from different worksheets. Since I am not focused on that part and have not much idea about those worksheet structures, I can't say anything for sure.

 

[njpamdnc]

Ok...I now have a better understanding of what you did. I do have other distinct content in each SIARQX page that formulas are referring to retrieve data from different worksheets. Before I copy a SIARQX to another I will simply deactivate the current SIARQX worksheet by performing the "Find and Replace" process I previously stated. In other words, there will only be one active SIARQX worksheet at a time while the other three SIARQX worksheets are deactivated.

 

[jalrs]

Hello @smozgur ,

I have this question in my head too, except for that overwritting part. I didn't copy your code or njpamdnc code. I came up with one on my own. But it's displaying me an issue when checking for next i value where I can't workaround it. Wonder if I can get some assistance here or need to open a new thread since this one is 419 days old.

Thanks.