A
anilsolipuram
Do you want bold the text in textfile or that in the original exce
file, you cannot bold text in textfile by using macr
file, you cannot bold text in textfile by using macr
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
thephoenix12
I would like to bold the text of the names only (row B) on the new excel
file that is created when the macro is run. Even though the original
text is bold, it is not on the new excel file. Any ideas?
file that is created when the macro is run. Even though the original
text is bold, it is not on the new excel file. Any ideas?
A
anilsolipuram
This will create excel file instead of textfile we previous had, makes
the text bold
Sub MACRO9()
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 cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub
the text bold
Sub MACRO9()
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 cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
For i = 0 To UBound(temp1)
Selection.Value = temp1(i)
ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub
T
thephoenix12
That makes everything in the new worksheet bold. Is there a way to make
only the names (obtained from column B, in this part of the code "temp =
temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)") bold, on the
new worksheet?
only the names (obtained from column B, in this part of the code "temp =
temp & Chr(10) & Range("b" & e_range.Row).Value & Chr(10)") bold, on the
new worksheet?
T
thephoenix12
Im thinking of doing something like this: the names are all under 15
characters, everything else is larger, and they all contain no numbers,
as compared to mostly everything else.
So what if we were to insert and If statement saying If cell has no
numbers and has less than 15 characters, Then Selection.Font.Bold =
True End If. As you can see though, I dont know how to do the first
part with the numbers and the characters.
characters, everything else is larger, and they all contain no numbers,
as compared to mostly everything else.
So what if we were to insert and If statement saying If cell has no
numbers and has less than 15 characters, Then Selection.Font.Bold =
True End If. As you can see though, I dont know how to do the first
part with the numbers and the characters.
A
anilsolipuram
try this now
Sub MACRO99()
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 cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & "bold" & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
MsgBox temp
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
Dim ch_bold As Variant
For i = 0 To UBound(temp1)
ch_bold = Split(temp1(i), "bold")
If (UBound(ch_bold) > 0) Then
Selection.Value = ch_bold(1)
ActiveCell.Font.Bold = True
Else
Selection.Value = temp1(i)
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub
Sub MACRO99()
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 cell to search")
Set range_input = Range(VAL)
For Each e_range In range_input
temp = temp & "bold" & Range("b" & e_range.Row).Value & Chr(10)
For Each W In Worksheets
W.Select
If W.Name <> sh_skip Then
If (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
MsgBox temp
Workbooks.Add
temp1 = Split(temp, Chr(10))
Range("a1").Select
Dim ch_bold As Variant
For i = 0 To UBound(temp1)
ch_bold = Split(temp1(i), "bold")
If (UBound(ch_bold) > 0) Then
Selection.Value = ch_bold(1)
ActiveCell.Font.Bold = True
Else
Selection.Value = temp1(i)
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\sheetname.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
Workbooks.OpenText Filename:="C:\sheetname.xls"
End Sub
T
thephoenix12
Hey, right now I am trying to clean this macro up so that, on the final
excel output there are no two blank spaces in a row...let me explain;
this is a sample output excel file
Person 1
Project 3
Project 6
Person 2
Project 1
Person 3
Project 7
The extra spaces come in because there is a space on the original excel
file, because of the way it is organized. So when the program outputs
the value of the corresponding cell in column b, it is only outputting
a blank cell. I am trying to write the program so that it skips cells
in column b when there are blank spaces, however I keep getting an
error message. Here is the program with what I have added in bold:
Sub ProjectSearch()
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 RANGE(\"B\" & E_RANGE.ROW).VALUE.CHARACTERS.COUNT > 0 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 (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
Workbooks.Add
Range("a1").Select
Selection.Value = "Title"
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 Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\test.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\test.txt"
End Sub
Do you have any ideas on what I should do?
Thanks,
-Steve
excel output there are no two blank spaces in a row...let me explain;
this is a sample output excel file
Person 1
Project 3
Project 6
Person 2
Project 1
Person 3
Project 7
The extra spaces come in because there is a space on the original excel
file, because of the way it is organized. So when the program outputs
the value of the corresponding cell in column b, it is only outputting
a blank cell. I am trying to write the program so that it skips cells
in column b when there are blank spaces, however I keep getting an
error message. Here is the program with what I have added in bold:
Sub ProjectSearch()
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 RANGE(\"B\" & E_RANGE.ROW).VALUE.CHARACTERS.COUNT > 0 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 (IsNumeric(Range(e_range.Address).Value) And
Range(e_range.Address).Value <> "") Then
temp = temp & W.Name & Chr(10)
End If
End If
Next
Next
Workbooks.Add
Range("a1").Select
Selection.Value = "Title"
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 Then
ActiveCell.Font.Bold = True
End If
ActiveCell.Offset(1, 0).Select
Next
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\srh.HSNPARCH\Desktop\test.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\test.txt"
End Sub
Do you have any ideas on what I should do?
Thanks,
-Steve
A
anilsolipuram
try with
if trim(Range("b" & e_range.Row).Value)<>"" Then
if trim(Range("b" & e_range.Row).Value)<>"" Then
A
anilsolipuram
try with
if trim(Range("b" & e_range.Row).Value)<>"" Then
if trim(Range("b" & e_range.Row).Value)<>"" Then
T
thephoenix12
Thanks that works
T
thephoenix12
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
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
A
anilsolipuram
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
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
T
thephoenix12
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
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
A
anilsolipuram
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
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
T
thephoenix12
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.
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.
A
anilsolipuram
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
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
T
thephoenix12
That works! Thanks so much anilsolipuram!!
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.