Merge Series ----- Copy Specific worksheet from different workbooks to one workbook as different worksheets

If you want to copy the specific worksheet from different workbooks and paste them into single workbook as different tabs or sheets.

Download Source File

Download Working Macro



Here is the code-

Option Explicit

Sub merge2()
' turn off alert to make the code run faster
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' declare variables
Dim fldpath, wkb As Workbook, wk As Worksheet
Dim fld As Object, fil As Object, fso As Object, j As Long
' open the folder dialog box to select the folder having the source files
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
Else
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
' run the loop between each files
For Each fil In fld.Files
' check if the file is in excel format
If Right(fil.Name, 5) = ".xlsx" Then
' open the workbook
Set wkb = Workbooks.Open(fil.Path)
For Each wk In wkb.Sheets
' use if condtion to match the sheet name which we want to copy from diffrent workbooks
If UCase(wk.Name) = UCase("Data") Then
wk.Copy Before:=ThisWorkbook.Sheets(1)
' change the sheet name as workbook name
ActiveSheet.Name = fil.Name
End If
Next
' close the source workbook
wkb.Close
End If
Next
End If
'Release the object etc
Set fso = Nothing
Set fld = Nothing
Set wkb = Nothing
' turn on the alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub