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

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" wrote:

> 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

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

"Joel" wrote:

>
> 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" wrote:
>
> > 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

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