Merge data from several files (same SheetName) into ONE File/Sheet

[jmsla...@gmail.com]

Hello,

Question and hopefully somebody can solve it :).
I've got a macro from the web which I use for merging from several files all sheets of them into one other file.

For example when I have;
- File1, SheetA, SheetB
- File2, SheetA, SheetB
The macro results in File3, SheetA, SheetB, SheetA(2), SheetB(2).

my whish is to change the macro is such a way that;

a) It select and copy from all files only a specific sheetname.
For example in the macro is mentioned 'SheetB' then it must copy only from all files the sheet with the name 'SheetB'.

b) It should copy from all selected Files/SheetB only from record 7 till last used record.

c) It should copy the selected data to ONE sheet ! That means that if the first sheet contains 1000 records then the copy of the next one should be paste at record 1000+1 and so on for the other files.

The actual 'normal' copy/merge macro is (see below);

Somebody can solve this. It should be great !! :)

regards, Johan

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

[Jeyakumar]

Hello,

Please download macro files from bottom of this page on this link.

https://www.rondebruin.nl/win/addins/rdbmerge.htm

1. you can merge if sheet names are same or not
2. you can merge only selected folders or including sub folders
3. you can paste as a link or as a values
4. you can paste one next to each horizontally or you can paste one by one below
5. you can select which sheet name should be merged if there are many sheets

only thing is headings to be same in all files.

after opening the file >> Enable Macros >> go to Data tab >> you will see the merge data at last

Regards,
Jeyakumar

[jmsla...@gmail.com]

Op zaterdag 16 oktober 2021 om 06:59:19 UTC+2 schreef Jeyakumar:

-------------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks for this advice :) :)

regards, Johan