Appending data from one sheet to another.

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

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
 
Back
Top