IF function

[Guest]

Hello,

I need help in creating a function. I have an Excel worksheet with 20 row of
information. Headings are: 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 Phillips]

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)

 

[Guest]

Bob,

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

Bagia

 

[Bob Phillips]

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, Insert>Module. 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 Tools>Macro>Macros..., 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)

 

[Guest]

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]

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)

 

[Guest]

Bob, I changed the code a bit because Row 1 is the heading so I don't want it
to create a worksheet from Row 1. I ran the module and it copied correctly,
but it doesn't include the headings for the worksheets. Anyway to incorporate
that in?

Thanks so much Bob!

 

[Bob Phillips]

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 = 2 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
.Rows(1).Copy sh.Range("A1")
j = 2
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)

 

[Guest]

You're a GOD sent Bob! I can't thank you enough.

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 = 2 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
.Rows(1).Copy sh.Range("A1")
j = 2
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)