How to merge 2 workbook. & worksheet.

  • Thread starter Thread starter Brijesh
  • Start date Start date
B

Brijesh

I am using MS Office XP, and have lots of different files containing the
data in the format name ; address ; A/c. No. ; Ph. No. ,
now i want to merge all the workbooks as well as worksheets in one file,

is there anyone who can help me or any idea for that,


Thanx in advance.
Brijesh Parmar
Admin Executive.
For, EPIC Ventures.
 
If all the workbooks are in one folder, this might do it. (If they're not in a
single folder, then copy them to a temporary folder and run the macro.)

Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim curWks As Worksheet
Dim wks As Worksheet
Dim myFileNames As Variant
Dim fCtr As Long
Dim nextWkbk As Workbook
Dim destCell As Range
Dim dummyRng As Range

Set curWks = Workbooks.Add(1).Worksheets(1)

myFileNames = Application.GetOpenFilename _
(FileFilter:="Excel files, *.xls", _
MultiSelect:=True)

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
With curWks
Application.EnableEvents = False
Set destCell = .Range("a1")
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr), _
ReadOnly:=True, UpdateLinks:=0)
For Each wks In nextWkbk.Worksheets
With wks
Set dummyRng = .UsedRange
.Range("a1", .UsedRange).Copy _
Destination:=destCell
End With
With curWks
Set destCell = .Cells(.UsedRange.Rows.Count + 1, "A")
End With
Next wks
nextWkbk.Close savechanges:=False
Next fCtr
End With
Application.EnableEvents = True
Else
MsgBox "Ok, Quitting"
End If

Application.ScreenUpdating = True

End Sub

When you're prompted for the files to combine, click on the first and ctrl-click
on subsequent.

This line:
..Range("a1", .UsedRange).Copy _
Starts at A1 through the bottom right corner.

If you have headers, you could change this or just delete the duplicate headers
when you're done.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top