Sunday, May 13, 2012

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"
End With
On Error Resume Next
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
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
' close the source workbook
End If
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


  1. Hi, Thanks a lot for your post.. It´s posible to copy and paste as values?

  2. @Raul yes we can do that can you please share the sample file and post your problem on our group!forum/excelvbamacros