Macro for Merging Excel Files in One Workbook with Overwriting

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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
 

Attachments

  • screenshot.png
    screenshot.png
    201.6 KB · Views: 13

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

njpamdnc

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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

BatCoder
Joined
Feb 28, 2002
Messages
1,418
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

New Member
Joined
Feb 16, 2019
Messages
34
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,380
Messages
5,641,796
Members
417,239
Latest member
AymericA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top