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

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

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

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