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-
' 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
.Title = "Choose the folder"
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
' change the sheet name as workbook name
ActiveSheet.Name = fil.Name
' close the source workbook
'Release the object etc
Set fso = Nothing
Set fld = Nothing
Set wkb = Nothing
' turn on the alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Sunday, May 13, 2012
Merge Series ----- Copy Specific worksheet from different workbooks to one workbook as different worksheets
Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...
Please make sure that you have the latest Version Tool Last Updated on 17 Feb, 2012 Version 1.6 English Version For Excel 2007 &...
Adding Marquee in Excel worksheet Step 1 Step 2 Example 1 Continuous scrolling Add below code module1 or any new module Sub r...