MERGE TWO DIFFERENT WORBOOKS?

[Guest]

I WOULD LIKE TO MERGE TWO DIFFERENT EXCEL WORKBOOK FILES INTO A SINGLE FILE.

THE HELP MENU SUGGESTS A PROCEDURE WHICH INVOLVES GOING TO THE TOOLS
DROPDOWN MENU AND CLICKING ON "MERGE AND COMPARE". HOWEVER THAT ITEM IS NOT
HIGHLIGHTED IN MY SPREADSHEET.

CAN ANYONE OUTLINE A PROCEDURE?

THANK YOU

 

[Dave Peterson]

I think you're going to have to be a little more explicit on what you want done.

Do you mean that you want all the worksheets in two different workbooks just
copied into one giant workbook--just copy those sheets to a new workbook.

Do you mean that you want all the data from all the worksheets copied into one
giant worksheet--just select the used range of each worksheet and paste at the
bottom of the used area of the giant worksheet.

Do you mean that you want two worksheets (in two workbooks) merged based on some
common key--maybe a bunch of =vlookup()'s would do it.

Depending on what you mean, you'll get different suggestions.

 

[jose.guzmanmontero]

In my case I need to do the second one but I have to do
that 180 times... so what we need is an automated way to
do it.

-----Original Message-----
I think you're going to have to be a little more explicit on what you want done.

Do you mean that you want all the worksheets in two different workbooks just
copied into one giant workbook--just copy those sheets to a new workbook.

Do you mean that you want all the data from all the worksheets copied into one
giant worksheet--just select the used range of each worksheet and paste at the
bottom of the used area of the giant worksheet.

Do you mean that you want two worksheets (in two

workbooks) merged based on some

 

[Dave Peterson]

One way if all your workbooks are in the same folder:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim myFileNames As Variant
Dim nextWkbk As Workbook
Dim wks As Worksheet
Dim fCtr As Long
Dim DestCell As Range
Dim RngToCopy As Range

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

If IsArray(myFileNames) Then
Application.ScreenUpdating = False
Set newWks = Workbooks.Add(1).Worksheets(1)
Set DestCell = newWks.Range("a1")
For fCtr = LBound(myFileNames) To UBound(myFileNames)
Set nextWkbk = Nothing
On Error Resume Next
Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr))
On Error GoTo 0
If nextWkbk Is Nothing Then
MsgBox "Error with: " & myFileNames(fCtr)
Else
For Each wks In nextWkbk.Worksheets
With wks
Set RngToCopy = .Range("a1", _
.Cells.SpecialCells(xlCellTypeLastCell))
End With

RngToCopy.Copy _
Destination:=DestCell

Set DestCell _
= newWks.Cells.SpecialCells(xlCellTypeLastCell) _
.EntireRow.Cells(1).Offset(1, 0)
Next wks
nextWkbk.Close savechanges:=False
End If
Next fCtr
Else
MsgBox "try again later!"
End If

Application.ScreenUpdating = True

End Sub

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

(When you're prompted to select the filenames, just click on the first and
ctrl-click on subsequent (or click on the first and shift-click on the last).

 

[Guest]

Thank you. Your suggestion was quite helpful and worked.
HOUSTON