I have Sheet 1 as my master list of infomation.

100023 red
100048 blue
100589 yellow

Sheet 2 contains infomation to add

FI 100027 purple
FI 100048 blue

Sheet 3 contains information to delete

FO 1000589 yellow

I need the master sheet to automaticly or by macro button
add any new files in Sheet 2. So it would only add the
purple row because blue is already in the list.

And Sheet 3 information needs to be deleted from the
master Sheet, so yellow would be deleted.

Any additions need to be appended to the bottom of the
master list.

The result would be in the master sheet:

100023 red
100048 blue
100027 purple

Simon,

This procedure should do what you asked for.

Sub AddAndRemove()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim r As Long
Dim c As Range

'Initialize variables
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

'Add non-matching rows from ws2 to ws1
r = ws1.Range("A1").CurrentRegion.Rows.Count + 1
For Each c In ws2.Range("A1").CurrentRegion.Columns(2).Cells
If Application.IsNA(Application.Match(c.Value, ws1.Range("A:A"), 0))
Then
ws1.Cells(r, 1).Value = c.Value
ws1.Cells(r, 2).Value = c.Offset(0, 1).Value
r = r + 1
End If
Next c

'Remove rows from ws1 that have a match in ws3
For r = ws1.Range("A1").CurrentRegion.Rows.Count To 1 Step -1
If Application.IsNumber(Application.Match(ws1.Cells(r , 1).Value,
ws3.Range("B:B"), 0)) Then
ws1.Rows(r).Delete
End If
Next r

End Sub

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden


"Simon" wrote in message
...
I have Sheet 1 as my master list of infomation.

100023 red
100048 blue
100589 yellow

Sheet 2 contains infomation to add

FI 100027 purple
FI 100048 blue

Sheet 3 contains information to delete

FO 1000589 yellow

I need the master sheet to automaticly or by macro button
add any new files in Sheet 2. So it would only add the
purple row because blue is already in the list.

And Sheet 3 information needs to be deleted from the
master Sheet, so yellow would be deleted.

Any additions need to be appended to the bottom of the
master list.

The result would be in the master sheet:

100023 red
100048 blue
100027 purple

Thank you very much, this works great.

Thank you,

Simon

-----Original Message-----
Simon,

This procedure should do what you asked for.

Sub AddAndRemove()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim r As Long
Dim c As Range

'Initialize variables
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

'Add non-matching rows from ws2 to ws1
r = ws1.Range("A1").CurrentRegion.Rows.Count + 1
For Each c In ws2.Range("A1").CurrentRegion.Columns
(2).Cells
If Application.IsNA(Application.Match(c.Value,
ws1.Range("A:A"), 0))
Then
ws1.Cells(r, 1).Value = c.Value
ws1.Cells(r, 2).Value = c.Offset(0, 1).Value
r = r + 1
End If
Next c

'Remove rows from ws1 that have a match in ws3
For r = ws1.Range("A1").CurrentRegion.Rows.Count To 1
Step -1
If Application.IsNumber(Application.Match(ws1.Cells
(r, 1).Value,
ws3.Range("B:B"), 0)) Then
ws1.Rows(r).Delete
End If
Next r

End Sub

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden


"Simon" wrote in
message
...
I have Sheet 1 as my master list of infomation.

100023 red
100048 blue
100589 yellow

Sheet 2 contains infomation to add

FI 100027 purple
FI 100048 blue

Sheet 3 contains information to delete

FO 1000589 yellow

I need the master sheet to automaticly or by macro
button
add any new files in Sheet 2. So it would only add the
purple row because blue is already in the list.

And Sheet 3 information needs to be deleted from the
master Sheet, so yellow would be deleted.

Any additions need to be appended to the bottom of the
master list.

The result would be in the master sheet:

100023 red
100048 blue
100027 purple



.

You are welcome, Simon!

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden

"Simon" wrote in message
...
Thank you very much, this works great.

Thank you,

Simon

-----Original Message-----
Simon,

This procedure should do what you asked for.

Sub AddAndRemove()

'Declare variables
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim r As Long
Dim c As Range

'Initialize variables
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")

'Add non-matching rows from ws2 to ws1
r = ws1.Range("A1").CurrentRegion.Rows.Count + 1
For Each c In ws2.Range("A1").CurrentRegion.Columns
(2).Cells
If Application.IsNA(Application.Match(c.Value,
ws1.Range("A:A"), 0))
Then
ws1.Cells(r, 1).Value = c.Value
ws1.Cells(r, 2).Value = c.Offset(0, 1).Value
r = r + 1
End If
Next c

'Remove rows from ws1 that have a match in ws3
For r = ws1.Range("A1").CurrentRegion.Rows.Count To 1
Step -1
If Application.IsNumber(Application.Match(ws1.Cells
(r, 1).Value,
ws3.Range("B:B"), 0)) Then
ws1.Rows(r).Delete
End If
Next r

End Sub

--
Ture Magnusson
Microsoft MVP - Excel
Karlstad, Sweden


"Simon" wrote in
message
...
I have Sheet 1 as my master list of infomation.

100023 red
100048 blue
100589 yellow

Sheet 2 contains infomation to add

FI 100027 purple
FI 100048 blue

Sheet 3 contains information to delete

FO 1000589 yellow

I need the master sheet to automaticly or by macro
button
add any new files in Sheet 2. So it would only add the
purple row because blue is already in the list.

And Sheet 3 information needs to be deleted from the
master Sheet, so yellow would be deleted.

Any additions need to be appended to the bottom of the
master list.

The result would be in the master sheet:

100023 red
100048 blue
100027 purple



.