Need to List Servers from one worsheet to another based on a condt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2


Your Help is greatly appreciated.
 
Sheet1

Servers Environment
devServer1 Dev
preprodServer1 PreProd
prodServer1 Prod
prodServer2 Prod

Sheet 2

Dev PreProd Prod

I need a code to evalate or compare the value from Environment column
(Sheet1) and append the server name under appropriate Column in Sheet2.

End Result for Sheet2 will be

Dev PreProd Prod
devServer1 preprodServer1 prodServer1
prodserver2
Click to expand...

Try this:

Sub ListServers()

Dim rCell As Range
Dim rFound As Range

For Each rCell In Sheet1.Range("B2:B5").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

Make sure your sheet's are codenamed Sheet1 and Sheet2. Watch for word wrap
in this message.
 
Its exactly what i wanted. Thanks you very much.

Now have two more things to take care of :

1.Run Macro automatically whenever the sheet is updated with new severs
2. no duplication of data on sheet2

I will greatly appreciate if you can help me out with this.

I will be researching meanwhile. Thanks Again.
 
Its exactly what i wanted. Thanks you very much.

Now have two more things to take care of :

1.Run Macro automatically whenever the sheet is updated with new severs
2. no duplication of data on sheet2

I will greatly appreciate if you can help me out with this.

I will be researching meanwhile. Thanks Again.
Click to expand...

You could delete everything on Sheet2 and run the macro whenever something
changes on Sheet1. You would be recreating the list every time, but
depending on the size of the list, that may be acceptable to you. If you
use the Worksheet_Change event, the macro will run every time you change any
cell on the sheet. You could limit that to certain columns though.

For instance, you could limit it to only columns A and B, then make sure
there's a Server for every Environment before the macro runs.

Think about that and I can help you with the code when you get the logistics
worked out.
 
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.

Thanks for responding.............
 
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.
Click to expand...

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.
 
Great...this worked out fine. i just customized it to my needs.

i was trying to sort the data now

Sheet2.Range("A2:D65536")..Sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

But it fails to sort the values in columns A to D.

Thanks Again.....that was great...i am taking all the credit on behalf of you.
 
i was trying to sort the data now

Sheet2.Range("A2:D65536")..Sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

But it fails to sort the values in columns A to D.
Click to expand...

I'm guessing you only have one Dev server, so it sorting - it's just that
nothing's changing. The data on Sheet2 isn't really suited to be sorted,
although it can be done. Two options: Sort the data on Sheet1, then it
will automatically be sorted on Sheet2. Sort each column on sheet2
independently, like

Range("B2:B65536").sort
Range("C2:C65536").sort
Range("D2:D65536").sort
and so on.
Thanks Again.....that was great...i am taking all the credit on behalf of you.
Click to expand...

No problem.
 
Back
Top