Macro to check 2 lists and copy data
 

Hi,

I was hoping that someone would be able to help me with the this?
I have a master spreadsheet and an update that is sent on a dailey basis. In
column a in the master spreadsheet is the unique reference:

A
M1
M2
M3
etc this goes does to M150

In the updates I received only some of these are included. again in the
update the reference is in column A and the updated info is in column B

I know that this can be done with vlookups but want to use a Macro instead
if this is possible.

Any help is greatly appreceiated

Macro to check 2 lists and copy data
 

Change names of Update sheet and Master Sheet as required

Sub combinedata()


Set MasterSht = Sheets("Master")
Set UpdateSht = Sheets("Update")

With UpdateSht
RowCount = 1
Do While .Range("A" & RowCount) < ""
Data = .Range("A" & RowCount)
Set c = MasterSht.Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find : " & Data)
Else
c.Offset(0, 1) = .Range("B" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With

End Sub

"Arran" wrote:

Hi,

I was hoping that someone would be able to help me with the this?
I have a master spreadsheet and an update that is sent on a dailey basis. In
column a in the master spreadsheet is the unique reference:

A
M1
M2
M3
etc this goes does to M150

In the updates I received only some of these are included. again in the
update the reference is in column A and the updated info is in column B

I know that this can be done with vlookups but want to use a Macro instead
if this is possible.

Any help is greatly appreceiated

Macro to check 2 lists and copy data
 

Hi Joel,

Thank you for your response, this is a great help but I forgot to mention
that they are in different workbooks

"Joel" wrote:

Change names of Update sheet and Master Sheet as required

Sub combinedata()


Set MasterSht = Sheets("Master")
Set UpdateSht = Sheets("Update")

With UpdateSht
RowCount = 1
Do While .Range("A" & RowCount) < ""
Data = .Range("A" & RowCount)
Set c = MasterSht.Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find : " & Data)
Else
c.Offset(0, 1) = .Range("B" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With

End Sub

"Arran" wrote:

Hi,

I was hoping that someone would be able to help me with the this?
I have a master spreadsheet and an update that is sent on a dailey basis. In
column a in the master spreadsheet is the unique reference:

A
M1
M2
M3
etc this goes does to M150

In the updates I received only some of these are included. again in the
update the reference is in column A and the updated info is in column B

I know that this can be done with vlookups but want to use a Macro instead
if this is possible.

Any help is greatly appreceiated

Macro to check 2 lists and copy data
 

the code below assumes the two workbooks are open. It is possible to modify
the code to automatically open and close the workbook(s).


from
Set MasterSht = Sheets("Master")
Set UpdateSht = Sheets("Update")
to
Set MasterSht = workbooks("book1.xls").Sheets("Master")
Set UpdateSht = workbooks("book2.xls").Sheets("Update")

or
Set MasterSht = workbooks("book1.xls").Sheets("Master")
Set UpdateSht = Thisworkbook.Sheets("Update")

"Arran" wrote:

Hi Joel,

Thank you for your response, this is a great help but I forgot to mention
that they are in different workbooks

"Joel" wrote:

Change names of Update sheet and Master Sheet as required

Sub combinedata()


Set MasterSht = Sheets("Master")
Set UpdateSht = Sheets("Update")

With UpdateSht
RowCount = 1
Do While .Range("A" & RowCount) < ""
Data = .Range("A" & RowCount)
Set c = MasterSht.Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find : " & Data)
Else
c.Offset(0, 1) = .Range("B" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With

End Sub

"Arran" wrote:

Hi,

I was hoping that someone would be able to help me with the this?
I have a master spreadsheet and an update that is sent on a dailey basis. In
column a in the master spreadsheet is the unique reference:

A
M1
M2
M3
etc this goes does to M150

In the updates I received only some of these are included. again in the
update the reference is in column A and the updated info is in column B

I know that this can be done with vlookups but want to use a Macro instead
if this is possible.

Any help is greatly appreceiated