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

swarupa

New Member
Joined
Jan 2, 2021
Messages
32
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)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Change xStrName = "Sheet1,Sheet3" to Sheet CodeName that you want
Change Path to your Source Path and final Path file
VBA Code:
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

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

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

New Member
Joined
Jan 2, 2021
Messages
32
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

Attachments

  • 1234.jpg
    1234.jpg
    60.6 KB · Views: 2

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If You also do Post #7 Recommendation, I don't know why? For me working without problem at all.
 

swarupa

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

Watch MrExcel Video

Forum statistics

Threads
1,130,146
Messages
5,640,392
Members
417,140
Latest member
whiteprose

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