On Wed, 21 Mar 2007 16:40:33 -0700, Mudragaddam
wrote:

There is several other data on the sheet that needs to be in place. these are
the columns in a particular data set. if it was possible to clear the data
under dev/int/preprod/prod everytime this macro is run to rewrite the list
again that would be the right direction for me (like an cleanup-verification
process).

And as you said implementing the worksheet_change for every update on sheet1
will make it perfectly automatic to update Sheet2 all the time.

I will greatly appreciate if you could put something as part of code to make
me understand better.


Put this in the worksheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Or Target.Column = 2 Then
If Not (IsEmpty(Me.Cells(Target.Row, 1).Value) And _
IsEmpty(Me.Cells(Target.Row, 1).Value)) Then

ListServers

End If
End If

End Sub

And change the other macro thusly:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range
Dim rRng As Range

Sheet2.Range("B1").CurrentRegion.ClearContents
Set rRng = Sheet1.Range("B2", Range("B65536").End(xlUp))

For Each rCell In rRng.Cells
Set rFound = Sheet2.Rows(1).Find(rCell.Value, , xlValues, xlWhole)

If Not rFound Is Nothing Then 'header exists
Sheet2.Cells(Sheet2.Rows.Count,
rFound.Column).End(xlUp).Offset(1, 0).Value = _
rCell.Offset(0, -1).Value
Else 'header doesn't exist
With Sheet2.Cells(1,
Sheet2.Columns.Count).End(xlToLeft).Offset(0, 1)
.Value = rCell.Value
.Offset(1, 0).Value = rCell.Offset(0, -1).Value
End With
End If
Next rCell

End Sub

You may still need to tweak this a bit, but it should get you started.

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com