How to append data of particular sheet from multiple excel files into one excel file?

[swarupa]

I have Thousands of excel files in one folder viz. X.
These excel files have many Sheets.
But I am interested only in “S” sheet of every excel files.
This “S” sheet contain data.
I want to append all data of “S” Sheet from every excel files of folder X into one excel file. (The Header of data contains in Sheet "S" of every excel files of Folder X is same)

 

[maabadi]

Try this:

Sub MergeSheets2()

Dim xStrPath As String, xStrName As String
Dim xStrFName As String, xArr As Variant
Dim xWS As Worksheet, xMWS As Worksheet, xTWB As Workbook
Dim xStrAWBName As String,  xI As Integer, Sh1 as Worksheet

On Error Resume Next

xStrPath = "C:\Users\swaroopa.bp\Desktop\A\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
Set Sh1 = Sheets("Summery")
xStrName = Sh1.Name
xArr = Split(xStrName, ",")
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
xTWB.SaveAs Filename:="C:\Users\swaroopa.bp\Desktop\C\Consolidation", FileFormat:=xlWorkbookNormal
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[jskasango]

It is working fine but the target file opens as hidden. I would like the same except that I want my sheets to be merged into ONE sheet after matching the column headers. If its ok with you, column A can contain the source file name. Thanks in advance.

 

[maabadi]

Please ask your question as New Thread & Never ask your question at others threads.
This is Rule & Also more Members can see your thread and try to answer to it.