Hello,

I need help in creating a function. I have an Excel worksheet with 20 row of
information. Headings a A1"Date of Comment; B1"Prospect"; C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this worksheet is
create multiple sheets - one for each "Name". I would like the other
worksheets to be populated from Sheet 1(master list) so that, for example, if
"name"=Jacobs then sheet 2 will populate the "Date of Comment", "Prospect",
"Name", "Notes", and "Deadline", line by line for all "Name"=Jacobs, and then
the same for all of the other listed 'names'.

Sample Data:

A B C D
E
1 2/4/05 Dave Jacobs schedule appt w/ Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work with kids

3 Sean Smith get info from
student Jul 2006
4 4/14/06 Tim Jackson clarify information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count).End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hello,

I need help in creating a function. I have an Excel worksheet with 20 row
of
information. Headings a A1"Date of Comment; B1"Prospect"; C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this worksheet
is
create multiple sheets - one for each "Name". I would like the other
worksheets to be populated from Sheet 1(master list) so that, for example,
if
"name"=Jacobs then sheet 2 will populate the "Date of Comment",
"Prospect",
"Name", "Notes", and "Deadline", line by line for all "Name"=Jacobs, and
then
the same for all of the other listed 'names'.

Sample Data:

A B C D
E
1 2/4/05 Dave Jacobs schedule appt w/ Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work with
kids

3 Sean Smith get info from
student Jul 2006
4 4/14/06 Tim Jackson clarify information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia

Bob,

Please don't be mad, but where do I place these codes? Sorry for being
ignorant.

Bagia

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count).End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hello,

I need help in creating a function. I have an Excel worksheet with 20 row
of
information. Headings a A1"Date of Comment; B1"Prospect"; C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this worksheet
is
create multiple sheets - one for each "Name". I would like the other
worksheets to be populated from Sheet 1(master list) so that, for example,
if
"name"=Jacobs then sheet 2 will populate the "Date of Comment",
"Prospect",
"Name", "Notes", and "Deadline", line by line for all "Name"=Jacobs, and
then
the same for all of the other listed 'names'.

Sample Data:

A B C D
E
1 2/4/05 Dave Jacobs schedule appt w/ Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work with
kids

3 Sean Smith get info from
student Jul 2006
4 4/14/06 Tim Jackson clarify information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia

Mad? Don't be daft.It is difficult to know how much or how little a poster
knows.

This is just a common old garden macro, so it goes in a standard code
module. To get at this, from Excel do an Alt-F11. This will take you into
the VBIDE. From here, insert a code module, InsertModule. Copy and paste
that code into there.

Then go back to the data sheet in Excel, and run the macro. To do this, use
menu ToolsMacroMacros..., select Test from the list, and hit Run.

Let us know how it goes.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Bob,

Please don't be mad, but where do I place these codes? Sorry for being
ignorant.

Bagia

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh =
Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count).End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hello,

I need help in creating a function. I have an Excel worksheet with 20
row
of
information. Headings a A1"Date of Comment; B1"Prospect"; C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this
worksheet
is
create multiple sheets - one for each "Name". I would like the other
worksheets to be populated from Sheet 1(master list) so that, for
example,
if
"name"=Jacobs then sheet 2 will populate the "Date of Comment",
"Prospect",
"Name", "Notes", and "Deadline", line by line for all "Name"=Jacobs,
and
then
the same for all of the other listed 'names'.

Sample Data:

A B C D
E
1 2/4/05 Dave Jacobs schedule appt w/
Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work with
kids

3 Sean Smith get info from
student Jul 2006
4 4/14/06 Tim Jackson clarify information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia

Hi Bob,

Can I bug you for another problem I have with this? It copied and pasted the
information onto its respective sheets, but it didn't copy all the data for
that particular solicitor. For example, for Solicitor LG there are 4 records
for Bob and 2 records for Dave. The macro copied only 1 record for Bob and 1
record for Dave. Anyway I can get it to copy all the records?

"Bob Phillips" wrote:

Mad? Don't be daft.It is difficult to know how much or how little a poster
knows.

This is just a common old garden macro, so it goes in a standard code
module. To get at this, from Excel do an Alt-F11. This will take you into
the VBIDE. From here, insert a code module, InsertModule. Copy and paste
that code into there.

Then go back to the data sheet in Excel, and run the macro. To do this, use
menu ToolsMacroMacros..., select Test from the list, and hit Run.

Let us know how it goes.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Bob,

Please don't be mad, but where do I place these codes? Sorry for being
ignorant.

Bagia

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh =
Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count).End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hello,

I need help in creating a function. I have an Excel worksheet with 20
row
of
information. Headings a A1"Date of Comment; B1"Prospect"; C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this
worksheet
is
create multiple sheets - one for each "Name". I would like the other
worksheets to be populated from Sheet 1(master list) so that, for
example,
if
"name"=Jacobs then sheet 2 will populate the "Date of Comment",
"Prospect",
"Name", "Notes", and "Deadline", line by line for all "Name"=Jacobs,
and
then
the same for all of the other listed 'names'.

Sample Data:

A B C D
E
1 2/4/05 Dave Jacobs schedule appt w/
Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work with
kids

3 Sean Smith get info from
student Jul 2006
4 4/14/06 Tim Jackson clarify information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia

Sorry mate, there was a bug in the code. Try this version

Sub TestIt()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hi Bob,

Can I bug you for another problem I have with this? It copied and pasted
the
information onto its respective sheets, but it didn't copy all the data
for
that particular solicitor. For example, for Solicitor LG there are 4
records
for Bob and 2 records for Dave. The macro copied only 1 record for Bob and
1
record for Dave. Anyway I can get it to copy all the records?

"Bob Phillips" wrote:

Mad? Don't be daft.It is difficult to know how much or how little a
poster
knows.

This is just a common old garden macro, so it goes in a standard code
module. To get at this, from Excel do an Alt-F11. This will take you
into
the VBIDE. From here, insert a code module, InsertModule. Copy and
paste
that code into there.

Then go back to the data sheet in Excel, and run the macro. To do this,
use
menu ToolsMacroMacros..., select Test from the list, and hit Run.

Let us know how it goes.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Bob,

Please don't be mad, but where do I place these codes? Sorry for being
ignorant.

Bagia

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim shThis As Worksheet

With ActiveSheet
iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If sh Is Nothing Then
Set sh =
Worksheets.Add(after:=Worksheets(Worksheets.Count) )
sh.Name = .Cells(i, "B").Value
j = 1
Else
j = sh.Cells(sh.Rows.Count).End(xlUp).Row + 1
End If
.Rows(i).Copy sh.Cells(j, "A")
Next i
End With

End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Bagia" wrote in message
...
Hello,

I need help in creating a function. I have an Excel worksheet with
20
row
of
information. Headings a A1"Date of Comment; B1"Prospect";
C1"Name";
D1"Notes"; and E1"Deadline". What I would like to do from this
worksheet
is
create multiple sheets - one for each "Name". I would like the
other
worksheets to be populated from Sheet 1(master list) so that, for
example,
if
"name"=Jacobs then sheet 2 will populate the "Date of Comment",
"Prospect",
"Name", "Notes", and "Deadline", line by line for all
"Name"=Jacobs,
and
then
the same for all of the other listed 'names'.

Sample Data:

A B C
D
E
1 2/4/05 Dave Jacobs schedule appt w/
Jacobs
Apr 2007
2 2/1/06 Dave Jacobs continue to work
with
kids

3 Sean Smith get info
from
student Jul 2006
4 4/14/06 Tim Jackson clarify
information
Aug 2006
5 7/1/06 Dave Jacobs Contact for more
info
Sep 2007

I hope this is clear...if not please let me know.

Thanks in advance,
Bagia