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

swarupa

New Member
Joined
Jan 2, 2021
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,585
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

KasangoJS

New Member
Joined
Mar 14, 2021
Messages
39
Office Version
  1. 2016
  2. 2013
  3. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,585
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,200
Messages
5,623,336
Members
415,968
Latest member
Chabal74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top