Solved: Illusive Macro

[gelpena]

Don't be so mean with your sample data if you want a robust solution.

The least I want to be is mean to you guys who have helped me so much!. How was I mean with my sample data?

[mdmackillop]

Too few data to check if duplicate names are returned. What else might go wrong that needs to be handled?

[gelpena]

Too few data to check if duplicate names are returned. What else might go wrong that needs to be handled?

You're right. I just thought It would be redundant. There's nothing else I can think of. Cosmetic details are not that important.

[gelpena]

That error will occur if there is no date in Sheet 2 to match that in found on Sheet 1

This has error handling added
[vba]Sub Test()
Dim r As Range, cel As Range, tgt As Range
Set r = Sheets(1).Range("B1:AC500").SpecialCells(xlCellTypeConstants)
On Error Resume Next
For Each cel In r
If IsDate(cel) Then
Set tgt = Sheets(2).Rows(1).Find(cel)
Sheets(2).Cells(Rows.Count, tgt.Column).End(xlUp)(2) = Sheets(1).Cells(cel.Row, 1)
If Err <> 0 Then
MsgBox "Date " & cel & " not found"
Err.Clear
End If
End If
Next
End Sub
[/vba]

Ok MD,
I duplicated your code(above) and changed the source sheet. called both macros from an outside sub and It's doing what I'm looking for it to do. Last thing I need help with is the duplication of the values. I want it to copy new updated data, not duplicate the existing one again. As it is right now, if I pressed the button ten times, it would copy the same data 10 times. See attached file.

[mdmackillop]

Do you want to add to previous data on the target sheet or to clear this and show only new values.

[gelpena]

Do you want to add to previous data on the target sheet or to clear this and show only new values.

The target sheet(Sheet2) should retain old data(unless deleted from source sheets(Sheet1,Sheet3). If no new values are added to source sheets then when button is pressed then nothing should happen. If new data is added to source sheets then target sheet should add the new data to the existing one. (If Ray is scheduled to work on Jan 20 (already in tgt sheet) and now John has been added to the schedule, then the macro would know Ray is already there and only John should be added. As it is now, it adds Ray again and then John).

[gelpena]

Thank you to all who looked at my code and provided assistance. Thank you mdmackillop very, very much for the code! Nsenor Udofa provided me with the last line. Thanks all!!!!


http://www.thecodecage.com/forumz/mi...plication.html