If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#31
|
|||
|
|||
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 |
#32
|
|||
|
|||
I think I got your point. Try this and let me know 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 W In Worksheets W.Select temp = temp & W.Name & Chr(10) For Each e_range In range_input If W.Name sh_skip Then If Trim(Range("b" & e_range.Row).Value) "" 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 = "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 -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
#33
|
|||
|
|||
That sort of works. This part needs to be changed: IF TRIM(RANGE(\"B\" & E_RANGE.ROW).VALUE) \"\" THEN I need it to be not the part from row b, but actually cells within the range that were entered. But if I try to just put IF TRIM(RANGE(E_RANGE).VALUE) \"\" THEN it gives me an error. Just to check to see if this worked though, I had my range be in column d, and replaced the "b" with "d" and it did work. So I just need to know what to put instead of just e_range. Thanks, Steve -- thephoenix12 ------------------------------------------------------------------------ thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
#34
|
|||
|
|||
minor change in code 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 W In Worksheets W.Select temp = temp & W.Name & Chr(10) For Each e_range In range_input If W.Name sh_skip Then If Trim(e_range.Value) "" 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 = "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 -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
#35
|
|||
|
|||
I don't know why it is doing this, but whatever worksheet I had last active before I run the macro, it only returns the people working on that project (worksheet), but says they are working on every single one. -- thephoenix12 ------------------------------------------------------------------------ thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
#36
|
|||
|
|||
Ok try it now 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 W In Worksheets W.Select temp = temp & W.Name & Chr(10) For Each e_range In range_input If W.Name sh_skip Then If Trim(W.Range(e_range.Address).Value) "" 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 = "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 -- anilsolipuram ------------------------------------------------------------------------ anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
#37
|
|||
|
|||
That works! Thanks so much anilsolipuram!! -- thephoenix12 ------------------------------------------------------------------------ thephoenix12's Profile: http://www.excelforum.com/member.php...o&userid=24336 View this thread: http://www.excelforum.com/showthread...hreadid=381213 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access Mail Merge to Word.doc files ? | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 18th, 2005 06:31 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Open & Read Newly created Table for Reporting ? | RNUSZ@OKDPS | Setting Up & Running Reports | 0 | May 9th, 2005 03:43 PM |
Sorry guyz.... | jim0861 | Powerpoint | 3 | April 15th, 2005 03:39 PM |
MsgBox Displays Too Early | gdtatuiowa | General Discussion | 7 | October 1st, 2004 10:06 PM |