If you want to merge data from multiple workbooks for specific sheets only and put them in separate tabs.
For example I have multiple workbooks stored in a folder. Like
and each workbook are having multiple worksheets Jan, Feb, Mar etc.
And you have created a new workbook with sheets Feb. and Mar only. Now you want to consolidate all Feb data into one sheet and all Mar Into one from multiple workbooks.
Feb - having all the consolidated data of Feb. worksheet from multiple workbooks.
Here is the code-
' DECLARE ALL VARIABLES AND ARRAYS
Dim fld, fil, FSO As Object
Dim WKB As Workbook
Dim wks As Worksheet
Dim j As Long, w As Long
Dim stcol As String, lastcol As String
stcol = "A" ' Change the starting column of ur data
lastcol = "C" ' Change the ending column of ur data
' SHOW FOLDER DAILOG BOX
.Title = "Choose the folder"
'.InitialFileName = "c:\"
On Error Resume Next
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"
' change sheet names here
shtnames = Array("Feb", "Mar") '\ add or remove sheets
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.StatusBar = True
Application.StatusBar = "Please wait till Macro merge all the files"
Set FSO = CreateObject("scripting.filesystemobject")
Set fld = FSO.getfolder(fldpath)
' browse through all files in source folder
For Each fil In fld.Files
If UCase(Right(fil.Path, 5)) = UCase(".xlsx") And fil.Name <> ThisWorkbook.Name Then
Set WKB = Workbooks.Open(fil.Path)
For j = LBound(shtnames) To UBound(shtnames)
For Each wks In WKB.Sheets
If wks.Name = shtnames(j) Then
w = WKB.Sheets(shtnames(j)).Range("a65356").End(xlUp).Row
' stcol - starting column of my range eg - a
'2 - as my data will start from row 2 because i do not want to copy headers again and again
'lastcol - end column of range eg - c
' w - last filled row in sheet/ ending row of my data
If w >= 2 Then
WKB.Sheets(shtnames(j)).Range(stcol & "2:" & lastcol & w).Copy _
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Download Source Files
Saturday, May 19, 2012
Merge Series ----- Merge data from multiple workbooks for specific sheets only and put them in seperate tabs.
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...