How to join files???

  • Thread starter Thread starter copeleto
  • Start date Start date
C

copeleto

Dear members,

I have sever exel files and I would like to join them (make just on
file), do you have any suggestions? or any program which already doe
it. All the files have the same format and the same information.

Thank
 
I prepared a customised programme for my brother.you have to change at
some palces
if necessary.this may not be very elegant programme. if there is a bug I
am sure you can find out and rectify.
open a new file
'copy names of files with full path and extension in sheet2 from C2 down
'mycomments are given after aprostrophe as in the case of vba
'copy this code in the new fiels vba. hope you succeed
--------------------------------
Option Explicit
Option Base 1
Public Sub collation()
Application.ScreenUpdating = False
Dim i As Integer
Dim j As Integer
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("sheet2")
Dim mysectors As Range
Dim sector1 As Range
Dim sectorlast As Range
Set sector1 = sh.Range("c2")
Set sectorlast = sh.Range("c2").End(xlDown)

Set mysectors = Range(sector1, sectorlast)
j = WorksheetFunction.CountA(mysectors)
MsgBox j
Dim wkbk() As String 'note this cannot be dim wkbi(j) as string
'the argument for wkbk for array dimension must be constant no variable
' that is why a redim statement later before the loop

redim wkbk(j) ' this is step required

For i = 1 To j
wkbk(i) = workbooks("Sheet2").Range("c2").Offset(i - 1, 0)

Workbooks.Open(wkbk(i)).Activate
ActiveWorkbook.Sheets(1).UsedRange.Copy
ThisWorkbook.worksheets("Sheet1").Range("a60000").End(xlUp).Offset(4,
0).PasteSpecial
Application.CutCopyMode = False
ActiveWorkbook.Close
Next i


ThisWorkbook.SaveAs "(write the full path,file name and extension)"

ThisWorkbook.workshseets("sheet1").Range("a1").Activate
MsgBox "collation is over. click ok "

Application.ScreenUpdating = True

End Sub

=====================================================
 
Back
Top