Need help making a Macro to find and replace

[dbouton]

Hi. I am needing assistance in creating a macro to find and replace contents of cells from one sheet to another. Sheet 1 contains the data I want to put into Sheet 2. Sheet 1, in short, contains a schedule for a person. I want to find in the log list of schedules in Sheet 2 the schedule for the person (Name is in Sheet 1, Cell A5) and replace their record in Sheet 2 with what is in Sheet 1. Can someone assist, please. I am just learning Macros and cannot figure it out.

Sheet 1, Row 5 A through P

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2, A through P
[TABLE="width: 500"]
<tbody>[TR]
[TD]Some Name[/TD]
[TD]Title[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Some Other Name[/TD]
[TD]Title[/TD]
[TD]9:00[/TD]
[TD]13:00[/TD]
[TD]9:00[/TD]
[TD]13:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[TD]OFF[/TD]
[/TR]
</tbody>[/TABLE]

^ Lets just say that the third row above is what I want to replace with Sheet 1, Row 5 B through P, but it can be anywhere in the list.

 

[XLRATOR]

Hi,
Try this out under a command button.

Dim rng As Range
For Each rng In Sheets(2).Range("B5:B1000").Cells
If rng.Value = Sheets(1).Range("B5").Value Then
Sheets(2).Range("D" & rng.Row & ":P" & rng.Row).Value = Sheets(1).Range("D5:P5").Value
End If
Next
MsgBox "Done!", vbInformation

Regards.
Karim

 

[dbouton]

Hmm... this doesnt work. All that happens is the "Done" information box pops open. When I check Sheet 2, nothing has changed...

 

[XLRATOR]

I am not sure you are doing the right thing.
This code expects the following:
In sheet1, Cell B5 contains the Person's name,C5 its title, D5:P5 contain its schedule.
In Sheet2, Range B5 Thru B1000 Contain the list of all your people, Column C their respective Titles, Columns D thru P their respective schedules.
So if you use this exact layout to begin with, then you will certainly get the good thing done at your discretion.

NB: Pay attention to the names of your worksheets (Sheet1 and Sheet2 in the code).

Regards.
Karim

 

[dbouton]

This code expects the following:
In sheet1, Cell B5 contains the Person's name,C5 its title, D5:P5 contain its schedule.
In Sheet2, Range B5 Thru B1000 Contain the list of all your people, Column C their respective Titles, Columns D thru P their respective schedules.
So if you use this exact layout to begin with, then you will certainly get the good thing done at your discretion.

Ahh, I have it working now. My issue was not with your module but rather with my placement of the cells. I have names in "A"... I adjusted the module as such and it is working like a champion! Now my users can update schedules without touching my master list of schedules!

Thank you very much.