MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Appending Function?

Posted by Matthew on September 04, 2001 9:50 PM


I am new to this so I am sorry if this has been covered but.....

I am trying to write a macro that will update certain cells within a workbook from cells within another. Problem is, The file from which the data is to be updated is likely to change, so I need to match row headings first to ensure the correct data is being updated.

So I am (trying) to write Something like:

For y = 1 to 500 'Whatever the number of rows are
For x = 1 to 500

If Sourceworkbook.xls.Cells(x,Column1)== Destinationworkbook.xls.Cells(y,Column1);

Then 'update data in following columns
else....'don't update data, try next match

I have no experience in VB, and limited experience in other languages so I am not sure what I am doing. I used your search function, but couldn't find anything under the keyword "appending" since this is what I am trying to do.

Any help appreciated,


Posted by Robb on September 05, 2001 5:31 AM


You could try this. It is written to check the values in Column C
of the 2 workbooks. If it finds a match, it copies the contents of
Columns D/E/F of SourceWB to Columns D/E/F of DestinationWB (in this example,
Sheet1 is used in both Books).

Sub IPIP()
Dim d As Range
With Workbooks("DestinationWB.xls").Worksheets("Sheet1")
For Each c In .Columns(3).Cells
r = c.Row
Set d = Workbooks("SourceWB.xls").Worksheets("Sheet1").Cells(r, 3)
If r > 500 Then Exit Sub
If .Cells(r, 3) = d.Value Then
.Cells(r, 3).Offset(0, 1) = d.Offset(0, 1)
.Cells(r, 3).Offset(0, 2) = d.Offset(0, 2)
.Cells(r, 3).Offset(0, 3) = d.Offset(0, 3)
End If
End With
End Sub

Any help?