Combining Worksheets into one

[trekker1218]

hello, You guys were extremely helpful with a previous problem several months ago. I am going to the well once more.
I have a few hundred workbooks that need to be combined into a single.
They all have exact same 8 sheets names within each workbook.
I need to keep the headers off one and combine all rows under it if possible. If not, I can sort and delete the duplicate headers.
All files are in same folder.

i tried multiple VBA and plugins. None seem to work properly.

Any Help would be greatly appreciated.

Sincerely,
Tino

 

[ravishankar]

Hi,
you can try these codes too. I have created a file called HNR_master.xlsm and a sheet called "control_panel". the following codes were written and tested on a small sample of half a dozen excel files. It has worked.

Sub collate_excelfiles()
Dim a As Long, c As String
Dim f As String, b As Integer, x As Integer, y As Integer
Workbooks("HNR_master.xlsm").Sheets("control_panel").Activate
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
f = Dir(Cells(1, 2) & "*.xls*")
Cells(2, 1).Select
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop


MsgBox "Listing is complete"
x = Sheets("control_panel").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "there are " & x - 1 & " files"
For a = 2 To x
Workbooks("HNR_master.xlsm").Sheets("control_panel").Activate
Workbooks.Open Filename:=Cells(1, 2) & Cells(a, 1)
For b = 1 To 8
c = Choose(b, "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8")
Sheets(c).UsedRange.Copy
Windows("HNR_master.xlsm").Activate
y = Sheets(c).Cells(Rows.Count, 1).End(xlUp).Row + 2
MsgBox "Adding data to " & y & " th row of sheet " & c
Sheets(c).Range("A" & y).PasteSpecial
Z = Sheets("control_panel").Cells(a, 1)
Windows(Z).Activate
Next b
Next a

MsgBox "complete"
End Sub

I have named the sheets as sheets1 to 8. Change it if your sheet names are different. the master file also should have all these sheets. Try it on a dozen files separately and convince yourself that it is working the way you want it before you use it on hundreds of files.
Ravi shankar