merge data macro

[Julie]

I am looking for a macro to merge data from a source workbook to a
destination workbook with a common key. However, the macro cannot reside in
either the source or the destination workbooks. The destination workbook is
received from an external source (format remains the same, only the data
changes) and the source workbook is exported from MSAccess which replaces the
previous file each time.

Hope someone can help.

 

[Sam Wilson]

Hi,

You could put something like the following in a third workbook or an add-in.
When run, it prompts the user for the location of the source & target
workbooks, opens them so you can do stuff, and then closes them. Without
knowing what your sheets contain I can't help with the merge...

sub test()

dim xwb as workbbook
dim wb as workbook

Dim fd As FileDialog

'Open source workbook

Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = False Then Exit Sub
sPath = fd.SelectedItems(1)
Set xwb = Workbooks.Open(sPath)

'Open target workbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
If fd.Show = False Then Exit Sub
sPath = fd.SelectedItems(1)
Set wb = Workbooks.Open(sPath)

'Do what you want with the workbooks

'Close workbooks
xwb.close
wb.close

end sub

Sam

 

[Julie]

Hi Sam,
Thanks for the response. I don't have a problem opening the two workbooks.
I have that part of the code sorted. See following example of what I am
trying to do:

Source Book has columns A through to G with key data in column A
Destination book's related columns are C, N, O, P, Q, R & AC with key data
in column C

I need to move or copy the data from columns B, C, D, E, F & G from the
source book into columns N, O, P, Q, R & AC in the destination book
respectively where the reference in column C of the destination book matches
the reference in column A of the source book.

Let me know if you need any more details.

Thanks again,
Julie

 

[Sam Wilson]

Hi,
Is there any aggregation involved - ie, are you moving from a detailed list
of data to a summary?

Are both sets of key data sorted, and are they in 1 - 1 correspondence, or
will one be a subset of the other etc.

There are various ways to go - either using vlookups, or by using vb to drop
the values in, it just depends on your set-up.

Sam

 

[Julie]

Hi Sam,

There is only 1 - 1 record matching. ie. The source sheet has an order
number (listed once), delivery date, delivery time and departure time etc....
The destination sheet has the same order number (listed once) with customer
information and blank columns for the delivery information which is stored in
the source sheet. I need to get the delivery information onto the
destination sheet for each matching order. Only one row of data in the
source sheet will match one row of data in the destination sheet. There are
some records in the destination sheet that will not have matching data in the
source sheet and as such should be left blank.

Hope this helps.

Cheers,
Julie