Merging two Excel Documents

[Guest]

Is it possible to do a merge between to Excel files like you do with word and excel? I would like to have one excel file as the main document with the field names and another excel file with data and merge the two into another like a mail merge?

 

[Frank Kabel]

Hi Phillip
not quite sure why you want to do this. If you have created a table
with headings (the field names) and the associated data below this
heading row. Why do you want a merge?

 

[Dave Peterson]

if you can keep track of the addresses in the "print" worksheet and the columns
that they originate, you could just loop through the rows in the sending
worksheet and plop in the values, calculate, print (or print preview??), then go
back for the next.

Option Explicit
Sub testme()

Dim mstrWks As Worksheet
Dim prtWks As Worksheet

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim myToAddresses As Variant
Dim myFromColumns As Variant
Dim addrCtr As Long

myToAddresses = Array("a3", "b9", "c10")

myFromColumns = Array("a", "e", "j")

If UBound(myFromColumns) <> UBound(myToAddresses) Then
MsgBox "Design error--same number of elements are required!"
Exit Sub
End If

Set mstrWks = Worksheets("sheet1")
Set prtWks = Worksheets("sheet2")

With mstrWks
FirstRow = 2 'headers????
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
For addrCtr = LBound(myToAddresses) To UBound(myToAddresses)
prtWks.Range(myToAddresses(addrCtr)).Value _
= .Cells(iRow, myFromColumns(addrCtr)).Value
Next addrCtr
Application.Calculate 'just in case!
prtWks.PrintOut preview:=True 'save some trees!
Next iRow
End With

'clean up that print worksheet
For addrCtr = LBound(myToAddresses) To UBound(myToAddresses)
prtWks.Range(myToAddresses(addrCtr)).ClearContents
Next addrCtr

End Sub

I used column A to find the last row in the sending worksheet.

I used these addresses in the print worksheet:
myToAddresses = Array("a3", "b9", "c10")
(Just add/change as many as you want.)

But be careful to match up the sequence:
myFromColumns = Array("a", "e", "j")

in my example, column A's data went to A3, E's data to B9 and J's to C10. Keep
them in nice order.

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