PDA

View Full Version : Solved: Join Sheets Cells By Common Field



blastpwr1970
10-08-2009, 10:20 AM
Hi to all,

Could somebody please help me I need to join rows from different sheets with common fields, See attachment example.

mdmackillop
10-08-2009, 02:50 PM
Option Explicit
Sub Macro1()

Dim WS As Worksheet, wsht As Worksheet
Dim i As Long, j As Long
Dim c As Range

Set WS = Sheets.Add
Sheets("Data1").Range("A:D").Copy WS.Range("A1")
For i = 2 To 3
Set wsht = Sheets("Data" & i)
wsht.Range("A1:D1").Copy WS.Range("A1").Offset(, (i - 1) * 4)
For j = 2 To WS.Cells(Rows.Count, 1).End(xlUp).Row
Set c = wsht.Columns(1).Find(WS.Cells(j, 1))
If Not c Is Nothing Then
c.Resize(, 4).Copy WS.Cells(j, 1).Offset(, (i - 1) * 4)
End If
Next
Next
End Sub

blastpwr1970
10-09-2009, 11:24 AM
Thank you, it works great, of course you already knew that.