combine 5 excel files into one worksheet

You need to say how you want the data combined, eg is the data summed or should it appear in separate columns? More info is needed.

no summation is needed. I just need to append the data from each column  and in one worksheet.

To make it clearer. I have five workbooks.Each workbook contain a worksheet with similar structure..# of columns. I d like to combine all the workbooks'worksheets into 1 worksheet and save it.

The following code allows you to choose the files to be combined, just multiple select in the file dialog. It then asks for an output file. If the filenames are predetermined you can fiddle with this part but it is OK for showing the method.

The data from each file is appended to the new file. If you want to continue to append you can change the code to use an existing filename and open it instead of creating a new workbook with the chosen filename.

Sub combinefiles()
Dim flist As Variant
Dim wbIN As Workbook
Dim wbOUT As Workbook
Dim outputname As String
Dim fileno As Integer
Dim outrow As Integer
  flist = Application.GetOpenFilename("Excel files,*.xls*", 1, "Select files to combine", , True)
  If IsArray(flist) Then
    outputname = Application.GetSaveAsFilename("c:\test1\CombinedData.xls", "Excel files,*.xls*", 1, "Choose file for combined data")
    If outputname = "FALSE" Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wbOUT = Workbooks.Add
    With wbOUT.Worksheets(1)
      For fileno = 1 To UBound(flist)
        Set wbIN = Workbooks.Open(flist(fileno))
        wbIN.Worksheets(1).UsedRange.Copy
        outrow = .UsedRange.Rows.Count
        If outrow > 1 Then outrow = outrow + 1
        .Paste .Cells(outrow, 1)
        wbIN.Close
      Next fileno
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    wbOUT.SaveAs outputname
  End If
End Sub


Geoff

My 5 files are in a folder .Which part of the code to change pointing tto the filepath?

OK, I will assume your 5 files are contained in the folder C:\Test Folder as in the question.
To simplify matters we will assume the files are named file1.xls, file2.xls...file5.xls

You could change the code to read:
Sub combinefiles()
Dim wbIN As Workbook
Dim wbOUT As Workbook
Dim outputname As String
Dim fileno As Integer
Dim outrow As Integer
     outputname = Application.GetSaveAsFilename("c:\test1\CombinedData.xls", "Excel files,*.xls*", 1, "Choose file for combined data")
    If outputname = "FALSE" Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wbOUT = Workbooks.Add
    With wbOUT.Worksheets(1)
      For fileno = 1 To 5
        Set wbIN = Workbooks.Open("C:\Test Folder\file" & format(fileno) & ".xls")
        wbIN.Worksheets(1).UsedRange.Copy
        outrow = .UsedRange.Rows.Count
        If outrow > 1 Then outrow = outrow + 1
        .Paste .Cells(outrow, 1)
        wbIN.Close
      Next fileno
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    wbOUT.SaveAs outputname
  End If
End Sub

Replace .xls with .xlsx if using Excel 2007 or later.
If you want a fixed output filename, use
outputname ="C:\test2\Outputfile.xls"
instead of the line outputname=application getsaveas... (or similar name as you need)

Note that I have based this on using a separate workbook containing the VBA code. You could insert a button of the default worksheet and have it run the macro. THere are many other ways which you can work out yourself I guess.

Geoff

Is it possible to place the VBA code in 1 of the 5 wkst and have it serve as the master. Then at the point of e,I can output in weather folder I may prefer?

You may know this but when I email this to the user and as user opens ita box open saying the level of security for macros to run is set to highand he has to enable it. An instruction  to set digital signatures and so and so

.
What do you think of this messages?

Any workbook with VBA code (macros) will be subject to the macro security settings. The user will need to set their level of macro security to low or medium. If set to medium they will have to click to allow macros when the workbook is opened. If low, then all macros are allowed. This is fine if the user only uses workbooks from trusted sources.

In Office 2003 I think the setting is under Tools/Macros/Security

In Office 2007 the workbook must be saved as a .xlsm file if it contains macros. The setting for macro security is under Trust Centre.

Alternatively you can apply a digital signature to the VBA but this is outside my experience.

Geoff

The digital signatures or certification stuff is beyond my mind. I'm reading in the .net about it and I don't getit. But your solution work! I have to research and ask experts regarding those issues. Thanks a lot!!

Very well done!