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...
If you want to add a new menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in ea...
Macro to Export Range in Json Format Option Explicit Sub export_in_json_format() Dim fs As Object Dim jsonfile Dim rangetoex...