alternative to msgbox

Here is the macro with what you recommended to do anilsolipuram, now
that I am trying to change up the macro.
When I run this, what it does is it lists a person from column b, and
then it just lists all the worksheet names. Let me try to explain what
I am trying to do now a little better. The spreadsheet is set up so
that peoples names are in column b, and each worksheet represents a new
project. When they are working on it, they have a number in column d,
or e, or whatever (depending on the week). What I would like to do now
is to do the reverse of what we had done earlier, which was create a
list with the peoples names and the projects (worksheets) they were
working on. I would like to create a list that has each project
(worksheet), and under each project, lists the people working on it
(corresponding cell will not be blank). This is why the W.Name part
should be first I think, before the part where it lists the people.

Sub PeopleSearch()
Dim W As Worksheet
Dim range_input, e_range As Range
Dim VAL, sh_skip, temp As Variant
sh_skip = "Summary" 'sheetname to skip
VAL = InputBox("Enter which range to search in:")
Set range_input = Range(VAL)

FOR EACH E_RANGE IN RANGE_INPUT
IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) \"\" THEN
TEMP = TEMP & CHR(10) & RANGE(\"B\" & E_RANGE.ROW).VALUE & CHR(10)
END IF
FOR EACH W IN WORKSHEETS
W.SELECT
IF W.NAME SH_SKIP THEN
IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) \"\" THEN
TEMP = TEMP & W.NAME & CHR(10)

END IF
END IF
NEXT
NEXT

Workbooks.Add
Range("a1").Select
Selection.Value = "PROJECTS PEOPLE ARE WORKING ON"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\testing.txt", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\Documents and
Settings\srh.HSNPARCH\Desktop\testing.txt"
End Sub


--
thephoenix12
------------------------------------------------------------------------
thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336
View this thread: http://www.excelforum.com/showthread...hreadid=381213