Need a macro to copy & delete data

[Blasting Cap]

I have a worksheet that has 3 tabs in it. Tab1 has updated data in it,
tab2 has deletions in it, tab3 has the original data in it.

Each row in each workbook has 10-12 columns of name & address info
associated with it, each line (Col A) has a unique record number that is
supposed to be in the 3rd tab.

What I need are 2 macros.

First macro is needed to go into tab2, iterate thru the rows, and if it
finds that row number in tab3 column A, delete it.

Second macro is an update macro. What it needs to do is to iterate thru
tab1, and if it finds that row number in tab3, to replace all but the
first column of data (the unique row number). If there's no match on
the number in column A, it needs to add the row (including the row
number) from tab1.

Can anyone help?

Thanks,

BC

 

[Joel]

Not usre from your descition if rows in 2nd or 3rd tab need to be deleted.
Again the problem with tab1 and tab3 in which tab do you delete the rows.
Also include Tab sheet names, it is easier to code using the name.

Alos, if the record number is the same in the two tabs, can't we just copy
the row not just everything except the record number? It is much easier to
coopy the whole row than part of the row.

 

[Blasting Cap]

Tab1 - the tab with the updates in it.
Tab2 - the ones which are to be deleted
Tab3 - the tab all the action is being taken on, the source data.

For example - on Tab3, when iterating thru the contents of Tab2
(deletes), if a record is found in Tab3 that also exists in Tab2, the
row in Tab3 gets deleted.

Regarding Tab1, if a row there is found in Tab3, the contents of the row
in Tab1 is overlaid over the matching row in Tab3. If the row isn't
found in Tab3, the data in the row on Tab1 gets added to Tab3.

I wasn't sure if overlaying the row number (the "key" I would be using
to match against) would cause problems by overwriting the key field.

BC

 

[Joel]

Sub deleterows()

With Sheets("tab3")
Sh3RowCount = 1
Do While .Range("A" & Sh3RowCount) <> ""
RecordNumber = .Range("A" & Sh3RowCount)
With Sheets("tab2")
Set c = .Columns("A:A").Find( _
what:=RecordNumber, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Sheets("tab3").Rows(Sh3RowCount).Delete
Else
Sh3RowCount = Sh3RowCount + 1
End If
End With
Loop
End With
End Sub
Sub updaterows()

With Sheets("tab3")
Sh3LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Sh3NewRow = Sh3LastRow + 1
End With

With Sheets("tab1")
Sh1RowCount = 1
Do While .Range("A" & Sh1RowCount) <> ""
RecordNumber = .Range("A" & Sh1RowCount)
With Sheets("tab3")
Set c = .Columns("A:A").Find( _
what:=RecordNumber, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Rows(c.Row).Copy _
Destination:=Sheets("tab1").Rows(Sh1RowCount)
Else
Sheets("tab1").Rows(Sh1RowCount).Copy _
Destination:=Sheets("tab3").Rows(Sh3NewRow)
Sh3NewRow = Sh3NewRow + 1
End If
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
End Sub

 

[Blasting Cap]

It works with the exception of overlaying the data in Tab1 (the updates).

I had wanted the updated (Tab1) info to replace the original/source data
in Tab3 if it got a hit there on the record number. If it didn't find
it, I wanted it to add it (which it does).

How do I find out what the Tab3 row number is if it finds it in it?

I know on the Tab1 sheet I'm processing it sequentially, and doing
lookups in Tab3, so the rownumber there may not be sequential. I need
not only to know the new row, but also the "found" row if I find the
Tab1 record number in Tab3.

What I think I need to do is in the section of the "
If Not c is Nothing then"
statement is to do a copy on the row from Tab1 to the "found" row in
Tab3. What should that statement look like?

 

[Joel]

I went in the wrong direction.. I copied 3 into 1 instead of 1 into 3

from
If Not c Is Nothing Then
.Rows(c.Row).Copy _
Destination:=Sheets("tab1").Rows(Sh1RowCount)
to
If Not c Is Nothing Then
Sheets("tab1").Rows(Sh1RowCount).Copy _
Destination:=.Rows(c.Row)