Adding row data from one Excel file, to another, when not existing

[PatK]

I have been wrestling with a problem for a few days, and have had varying
success, but I have officially given up, as I have gone far afield, I fear,
to my original goal. Here is what I am trying to do:

- One workbook, Two Worksheets (ML and SL)
- SL has a subset of data of what is one ML
- Users add info to ML, and thus, we then need to create a corresponding row
on SL
- ML and SL have what I would call a common "key" field in Column A (call it
ID).

Let say ML has rows with these IDs:
001
002
003
005
009
010

SL has rows with these IDs:
001
002
003
009

I want to create a VBA routine that will open up ML and do what I call a
Vlookup back to SL, using ID. If ML.ID is not in SL, then I want to add a
row of data in SL, starting with the ID. So, in above case, if I ran the
routine, it would add 005, 009, and 010 to SL, if all worked as I want.

Basically, it is a synchronization issue (but note, all other cells in the
worksheets are different so a simply copy of all data won't work).

As always, thanks all...this is the best source of expert help!

Patk

 

[Joel]

Sub Synchronization()

With Sheets("SL")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With
With Sheets("ML")
MLRowCount = 1
Do While .Range("A" & MLRowCount) <> ""
ML_ID = .Range("A" & MLRowCount)
With Sheets("SL")
Set c = .Columns("A").Find(what:=ML_ID, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Sheets("ML").Rows(MLRowCount).Copy _
Destination:=.Rows(NewRow)
NewRow = NewRow + 1
End If
End With
MLRowCount = MLRowCount + 1
Loop
End With

End Sub

 

[PatK]

From what I have been learning, boy...this looks like it should work. I
shall try it tomorrow and get back to you. I had similar logic, but was
getting stuck on the Set C = .Column....... line as I had no idea what the
parameters meant. I also think I was trying to get too fancy with ranges :-o

More to come...thanks a MILLION Joel!

Patk

 

[PatK]

Sorry..couldn't wait. I gave it a shot and it worked quite well (so much
better than the NO PROGRESS I had been making. Here is the code, thus far.
Only changes were for the actual WS names, which I abbreviated in the initial
post. It definitely insert rows from one page to the other. I have to
reduce the amount of data, because initially, all I wanted was the ML_ID to
go over (I have another function that populates cells after column A). But
man...this is so great...I shall do a bit of auditing, and let ya
know...thanks Joel

Patk

Sub Synchronization()

With Sheets("Sunset-Plan")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With
With Sheets("HPSC")
MLRowCount = 1
Do While .Range("A" & MLRowCount) <> ""
ML_ID = .Range("A" & MLRowCount)
With Sheets("Sunset-Plan")
Set c = .Columns("A").Find(what:=ML_ID, LookIn:=xlValues,
lookat:=xlWhole)
If c Is Nothing Then
Sheets("HPSC").Rows(MLRowCount).Copy _
Destination:=.Rows(NewRow)
NewRow = NewRow + 1
End If
End With
MLRowCount = MLRowCount + 1
Loop
End With

End Sub