Excel VBA - Inserting associated values in a report Please Help :)

[JT]

Here's my sample data:
Column A, Column E on Worksheet ("CO")
"555-555-5555","Jim"
"123-456-7890","Jim"
"987-654-3210","Jim"
"444-444-4444","Kandy"
"333-333-3333","Kandy"
"222-222-2222","Steve"

Hi all I could really use some help on this. I'm printing reports in
excel based on values in a worksheet. The report desired prints the
name of the person along with all phone #'s associated with that
person.. Therefore the first report would Generate a page that says
"Jim" and the 3 phone numbers above associated with him, and then the
second would say Kandy with her two numbers and the last Steve with
one.

I'm using VBA to do this because this is basically automating an
annoying process here at work. So far I can get the report to print
for each unique name, but I'm having an issue figuring how out to add
those related phone numbers to each report. Here's the code I'm using:

Private Sub CommandButton1_Click()
Dim main, telephone As Range, i, k As Integer

i = 0
k = 1

Set main = Sheet1.Range("E" & k)
Set telephone = Worksheets("CO").Range("A27:I33")

Worksheets("CO").Range("B9").Value = Sheet1.Range("E" & k).Text
'name of person

Do While main.Offset(i, 0) <> ""
If Sheet1.Range("E" & k).Text <>
Worksheets("CO").Range("B9").Value Then
Worksheets("CO").PrintOut
Worksheets("CO").Range("B9").Value = Sheet1.Range("E"
& k).Text 'update name
If Sheet1.Range("E" & k + 1).Text = "" Then
Worksheets("CO").PrintOut
End If
k = k + 1
i = i + 1
ClearCourtOrder
Loop

End Sub

That prints the reports fine but can anyone suggest how to add the
associated phone numbers onto the report as well? Ideally, I set up a
range called telephone from A27:I33 on "CO" and would like the numbers
listed in that range only. I really appreciate any insight on how to
approach this.

 

[Leith Ross]

Hello JT,

Could you expalain or show how you want the names and numbers formatted
on the worksheets? In the number range of A27:I33 will the name be
followed by the numbers on the same row or some other arrangement? Let
me know and I can help you this.

Sincerely,
Leith Ross

 

[JT]

Hi Thanks a lot Leith!

I'm printing an invoice to each customer basically, and on the "report"
I generated on worksheet "CO" I have the name going to cell B9. I.e.
Jim, Kandy, Steve.

Then I have some other data being put elsewhere on the invoice, and the
only other bit that's dynamic are the telephone #'s associated with
that customer. I have the cells A27:I33 allocated for those phone
numbers to be printed in that range. In the example data, I have only
3 phone numbers max listed with a customer, but actually it's more like
10 - 18 per customer, so the desired output in A27:I33 would be:

777-555-5555 000-555-5555
666-555-5555 000-333-3333
555-555-5555 etc
444-555-5555
333-555-5555
222-555-5555
111-555-5555

So I guess A27:I33 would allow a max of 63 numbers in that range. I
suppose the cell could be formatted to indent say 3 chars so that the
#'s don't seem to run altogether.. what are you thoughts?

Thank you very much,

--JT