merge 24 seperate workbooks into one.

[Lasloja]

I have 24differnet workbooks each with one sheet with data. Each sheet has the sae headers with differen amounts of info under the headers. I am looking for a quick way to get all 24 sheets in to one workbook instead of copying and paste. Thank you for your help

[Alf]

instead of copying and paste

You can't avoid that but a macro could do the job in a matter of seconds. Still you need to give a bit more information. What kind of excel files are you interested in ".xls", ".xlsx" or ".xlsm" or a mixture of all 3 kinds?

Also are all the 24 files in the same folder or could there be more excel files in that folder you don't wish to "import"? Do you wish to have the header imported with each file or just the first header?

To get the best solution to your problem I would recommend you upload at least one sample file.

Alf

[Lasloja]

All 24 workbooks are in one folder. file ext is xls. All files are the same headers but i would like to bring the headers over so i can ID where they came from

[Alf]

with one sheet with data

?

As the uploaded workbook contains 3 sheets I assume that it is the sheet "Inventory Report" that you are interested in. As this is a pivot table I have not managed to copy both pivot formats and values. So for what it's worth here is a macro for copying the values.

The line

sPath = "F:\Temp\"

must be changed to the folder name and hard disk where you have the excel files you are interested in.

Option Explicit
Sub ProcessAllFiles()
 Dim sPath As String
 Dim Wb As Workbook
 Dim sFile As String
 Dim i As Integer
    sPath = "F:\Temp\"
    sFile = Dir(sPath & "*.xls")   'changing "*.xlsx" to "*.xl??" will find all files
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        Do While sFile <> ""
        Set Wb = Workbooks.Open(sPath & sFile)
        Sheets("Inventory Report").Activate
        ActiveSheet.UsedRange.Copy
        Wb.Close SaveChanges:=False
        i = ActiveSheet.UsedRange.Rows.Count
        If i = 1 And Range("A1") = "" Then
        Range("A1").PasteSpecial Paste:=xlPasteAll
        Else
        Range("A" & i + 1).PasteSpecial Paste:=xlPasteAll
        End If
      Application.CutCopyMode = False
    sFile = Dir
    Loop
   Columns("A:I").Columns.AutoFit
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

In order to keep the pivot format one could perhaps copy the "Inventory Report" sheet from each of the 24 excel files and paste those in a "Master file". This would give you a master file with 24 sheets, one from each file.

Alf