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.
Subscribe to: Post Comments (Atom)
Import data from SQL
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...
Post a Comment