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:
Change xStrName = "Sheet1,Sheet3" to Sheet CodeName that you want
Change Path to your Source Path and final Path file

Sub MergeSheets2()

Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
 
xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"
 
xArr = Split(xStrName, ",")
 
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
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

 

[swarupa]

No its not work.
I want to append data from specific sheet i.e. Sheet2 from every excel files. All These excel files in Folder "A".
After merging data from Sheet2 of all excel files new combined file is save to "C:\Users\swaroopa.bp\Desktop\C\Consolidation"

 

[maabadi]

Are You Change Source Path xStrPath = " C:\Users\DT168\Desktop\KTE\" to your source file Path.

AND
Change xStrName = "Sheet1,Sheet3" to xStrName = "Sheet2"

 

[swarupa]

I changed code accordingly my path and sheet name by creating button in Append.xlsm file. When code is running it creates consolidation file.
But the consolidation file is same as Append.xlsm file. It is not merging "Sheet2" of all excel files from Folder "A".

 

[maabadi]

For me working well.
what is your sheet code name (not tab name that see at excel window) at VBA window.
if don't working please upload your code. And take image from source worksheet at VBA window to see what is exact Sheet code name at VBA
For Example I Uploaded one image. Number 1 Part is sheet code name that I used at code & Number 2 is sheet name as Excel window (sheet tab name)
Try this:

Sub MergeSheets2()

Dim xStrPath As String, xStrName As String
Dim xStrFName As String, xArr As Variant
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
 
xStrPath = "C:\Users\swaroopa.bp\Desktop\A\"
xStrName = "Sheet2"
 
xArr = Split(xStrName, ",")
 
xArr = Split(xStrName, ",")
 
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
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

 

[maabadi]

if you use sheet tab name then change xStrName = "Sheet2" to xStrName = Sheet2.Name

 

[swarupa]

no its not working

 

[maabadi]

If You also do Post #7 Recommendation, I don't know why? For me working without problem at all.

 

[swarupa]

Dear maabadi,
I have 100 workbooks in folder “A”. Each workbooks having following sheets.
July, August, September, October, November, December, Summery.

I have important data in “Summery” sheet.
I want to merge/append data of “Summery”sheet of each 100 workbooks.