changing result worksheet of macro
 

Hi,

I have a macro that, when given a range of cells, it searches through
multiple worksheets to see when those cells are not blank. It then
displays a new worksheet with the results. Here is an example result
worksheet:

Sheet 1:
Cell 1
Cell 5

Sheet 2
Cell 3
Cell 4

Sheet 3
Cell 1

etc...

The problem I am having is that when I have a range that contains
multiple columns, I would like the result worksheet to look like this:
(range: D1:E9)

Sheet 1
Cell 1
Cell 3

Sheet 1
Cell 1
Cell 2

Sheet 2
Cell 5
Cell 9

Sheet 2
Cell 4
Cell 6

etc...

(So for each column, there is a new sheetname for the results to be
under)
The way I have the macro written however, it does not do this. My
macro's result would look like this:

Sheet 1
Cell 1
Cell 1
Cell 2
Cell 3

Sheet 2
Cell 4
Cell 5
Cell 6
Cell 9

I cannot think of how to change my macro to make it the way I would
like it. If you have any suggestions please respond. Here is my macro
so you can see what it looks like:

-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: (ex. D9:D62)")
Set range_input = Range(VAL)
FOR EACH W IN WORKSHEETS
W.SELECT
IF W.NAME < \"SUMMARY\" THEN
TEMP = TEMP & CHR(10) & W.NAME & CHR(10)
END IF

FOR EACH E_RANGE IN RANGE_INPUT
IF W.NAME < SH_SKIP THEN

IF TRIM(W.RANGE(E_RANGE.ADDRESS).VALUE) < \"\" AND
TRIM(W.RANGE(E_RANGE.ADDRESS).VALUE) < \"0\" THEN
TEMP = TEMP & W.RANGE(\"B\" & E_RANGE.ROW).VALUE & CHR(10)

END IF
END IF
NEXT
NEXT
Workbooks.Add
Range("a1").Select
Selection.Value = "CURRENT PROJECTS LIST"
Selection.Font.Bold = True
temp1 = Split(temp, Chr(10))
Range("a2").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
If ActiveCell.Characters.Count 13 Or Not only_text(ActiveCell) Or
ActiveCell.Value = "RM UCONN SFA" Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:= _
"blah", _
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="blah"
End Sub-


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