Taking one value from a list on one sheet and duplicating it multiple times in a list in another

[RichieA]

Hi all - a data entry question but on a scale that needs automation/formula to build:

In the example below, Sheet 2 is my master source of clients and values and Sheet 1 I am building from scratch as a sort of Account Plan/Summary "database" with actions (in the absence of a company CRM system). You can see from Sheet 2 that there is One row entry per Client name (of which there are 600+). In Sheet 1 though, I need to reflect these but need circa 14 row entries per client. Is there an easy way of automating this with a formula? Macros I know NOTHING about!

So I could build it so that the 14 Offices in Column B in sheet 1 are already entered and copied down the relevant number of times (600+) and then need to populate Column A with the relevant Client in blocks of 14. Does that make sense?

Or is there a way to tackle building it with 14 row entries per Client (from Column A in Sheet2) AND a corresponding Office entry next to it (from the column headers in Sheet 2?

Many thanks

RichieA


[TABLE="width: 554"]
<tbody>[TR]
[TD="class: xl415, width: 184, bgcolor: transparent"]Sheet 1
[/TD]
[TD="width: 116, bgcolor: transparent"][/TD]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #CCC0DA"]Client
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Office
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Value
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Next Action
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Latest Event
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£25
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Arrange meet
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Sold 1000 units
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£63
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£564
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Supply goods
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Visit complete
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£34
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Shut down
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£67
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]Austria
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£42
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Belgium
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£40
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Canada
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£32
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Rugby
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"]Lunch
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Denmark
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£56
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="class: xl419, width: 116, bgcolor: #F2F2F2"]Egypt
[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]£40
[/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="class: xl418, bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: transparent"]Sheet 2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A1
[/TD]
[TD="bgcolor: transparent"]B1
[/TD]
[TD="bgcolor: transparent"]B2
[/TD]
[TD="bgcolor: transparent"]B3
[/TD]
[TD="bgcolor: transparent"]B4
[/TD]
[TD="bgcolor: transparent"]B5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Austria
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Belgium
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Canada
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Denmark
[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Egypt
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Alpha Inc
[/TD]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]63
[/TD]
[TD="bgcolor: transparent, align: right"]564
[/TD]
[TD="bgcolor: transparent, align: right"]34
[/TD]
[TD="bgcolor: transparent, align: right"]67
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Bravo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Charlie Inc
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]29
[/TD]
[TD="bgcolor: transparent, align: right"]45
[/TD]
[TD="bgcolor: transparent, align: right"]61
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Delta Inc
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[TD="bgcolor: transparent, align: right"]568
[/TD]
[TD="bgcolor: transparent, align: right"]98
[/TD]
[TD="bgcolor: transparent, align: right"]52
[/TD]
[TD="bgcolor: transparent, align: right"]231
[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: #F2F2F2"]Echo Inc
[/TD]
[TD="bgcolor: transparent, align: right"]97
[/TD]
[TD="bgcolor: transparent, align: right"]238
[/TD]
[TD="bgcolor: transparent, align: right"]56
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]47
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RichieA]

OK -so whilst I can't supply company data, I have sanitised, but the layout isidentical to the original with data in the correct Columns etc.


Sheet 1 named "Clients & Teams". This is the sheet that Ineed to insert the multiple entries (17) per client name that is held withinSheet 2 named "Clients". The client names need to go in Column D. InColumn E, I have shown the list of 17 company teams through almost 2 cycles toshow the pattern. So when correctly populated there should be in Column D 17 entries for Abraham, against each of the Teams, then 17 entries for Allan and so on.

Sheet 2 "Clients". Here, this is the simple list of 20 clientnames for the example. The actual data runs to 600+. For certainty, it will beheld as a separate sheet within the same workbook as Sheet 1.

Mick - massively grateful for your input. As a learning exercise, if there isthe potential to add some narrative explaining what your code is doing, thatwould help me understand the workings a little better. I'd like to be able toget to a point of self-sufficiency rather than relying on the awesome advicefrom helpful people on this site (though that may be very aspirational!).

If you foresee any difficulty with the way I have represented the data - ie thecode won't work because it's still not a complete sheet, please let me know.With my basic logic, I assume that I could make small changes to the code toreflect more rows or a different sheet name - in the same way one would withformulas. But I have no code knowledge, so not sure if it works the same way.

Many thanks in anticipation

RichieA


SHEET1
[TABLE="width: 503"]
<colgroup><col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;" width="69"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" width="42"> <col style="width: 179pt; mso-width-source: userset; mso-width-alt: 8704;" span="2" width="238"> <tbody>[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 69, bgcolor: transparent"][/TD]
[TD="width: 42, bgcolor: transparent"][/TD]
[TD="class: xl413, width: 238, bgcolor: transparent"]Progress & Actions[/TD]
[TD="class: xl413, width: 238, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent"]A[/TD]
[TD="class: xl414, bgcolor: transparent"]B[/TD]
[TD="class: xl414, bgcolor: transparent"]C[/TD]
[TD="class: xl414, bgcolor: transparent"]D[/TD]
[TD="class: xl414, bgcolor: transparent"]E[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #9BBB59"]£ 2014 YE[/TD]
[TD="class: xl415, bgcolor: #9BBB59"]£ 2015 H1[/TD]
[TD="class: xl416, bgcolor: #9BBB59"]Progress[/TD]
[TD="class: xl417, bgcolor: #9BBB59"]Client[/TD]
[TD="class: xl417, bgcolor: #9BBB59"]Team[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl419, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"]2[/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"]Team1[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl419, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team2[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl419, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team3[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team4[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team5[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team6[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team7[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team8[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team9[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team10[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team11[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team12[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl423, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team13[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team14[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team15[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team 16[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team17[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"]Team1[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team2[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team3[/TD]
[/TR]
[TR]
[TD="class: xl418, bgcolor: transparent"] [/TD]
[TD="class: xl424, bgcolor: transparent"] [/TD]
[TD="class: xl420, bgcolor: transparent"] [/TD]
[TD="class: xl421, bgcolor: transparent"] [/TD]
[TD="class: xl422, width: 238, bgcolor: transparent"]Team4[/TD]
[/TR]
</tbody>[/TABLE]


SHEET2

[TABLE="width: 296"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 248pt; mso-width-source: userset; mso-width-alt: 12105;" width="331"> <tbody>[TR]
[TD="class: xl413, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl413, width: 331, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #006A4D"]RANK[/TD]
[TD="class: xl415, bgcolor: #006A4D"]CLIENT[/TD]
[/TR]
[TR]
[TD="class: xl416, bgcolor: #006A4D"]Rank[/TD]
[TD="class: xl416, bgcolor: #006A4D"]Company[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl414, bgcolor: transparent"]Abraham[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl414, bgcolor: transparent"]Allan[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl414, bgcolor: transparent"]Alsop[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl414, bgcolor: transparent"]Anderson[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl414, bgcolor: transparent"]Arnold[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl414, bgcolor: transparent"]Avery[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl414, bgcolor: transparent"]Bailey[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl414, bgcolor: transparent"]Baker[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl414, bgcolor: transparent"]Ball[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl414, bgcolor: transparent"]Bell[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl414, bgcolor: transparent"]Berry[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl414, bgcolor: transparent"]Black[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl414, bgcolor: transparent"]Blake[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl414, bgcolor: transparent"]Bond[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl414, bgcolor: transparent"]Bower[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl414, bgcolor: transparent"]Brown[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl414, bgcolor: transparent"]Buckland[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl414, bgcolor: transparent"]Burgess[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl414, bgcolor: transparent"]Butler[/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl414, bgcolor: transparent"]Cameron[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

From what you now show in sheet (1) & (2), directs me to alter my code so that each "Clients" name (from Sheet2) would be copied down column "D" of sheet1, 17 times. Is that what you want ????, And if so where would I get the "Team" names from ????

Whereas the original code Looked at each client in sheet2, then looked at the exact number of entries in that row, and took those entries along with the appropriate headers and places them in sheet1 columns A,B and C. Can I assume you do not now want this and would go for the Code in Paragraph above, ???

 

[RichieA]

Ah - I see! Apologies for the confusion.

In answer to your question in para 1 "From what you now show in sheet (1) & (2), directs me to alter my code so that each "Clients" name (from Sheet2) would be copied down column "D" of sheet1, 17 times. Is that what you want ????" In short, Yes. Because it turns out my master sheet (Sheet2) only includes 14 of the 17 Teams I require, I figured it was easier to manually enter the 17 teams in Sheet1 and then just copy the sequence down multiple times using the "fill handle". I guess that makes it easier - with the code not having to compute how many entries in another sheet etc?

So I think the code in Para1 would suffice.

(Let me know if you think my method of copying down the sequence of 17 teams using the fill handle is long-winded and could be done simply using anothetr method).

Many thanks Mick

R

 

[MickG]

If you specify what the teams are, say, in sheet 2, the code could easily fill them, while it filling the clients. Just as you like !!!!
Let me know and I'll send some code.

 

[RichieA]

OK - so use Sheet 2 B703:719 for the 17 Team names, and I'll populate those cells before running the code in the actual worksheet.

Thanks for your patience!

R

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Aug04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, TeamRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Clients")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("B3"), .Range("B1").End(xlDown))
    [COLOR="Navy"]Set[/COLOR] TeamRng = .Range("B703:B719")
[COLOR="Navy"]End[/COLOR] With
 c = 2
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]With[/COLOR] Sheets("Clients & Teams")
            TeamRng.Copy .Cells(c, "D")
           .Cells(c, "C").Resize(17) = Dn
        [COLOR="Navy"]End[/COLOR] With
 c = c + 17
 [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[RichieA]

Awesome Mick - that works, thank you! I had to tweak slightly to make the data appear in columns D & E instead of C & D - but that made me try and understand the code, so very useful.

Mick - many thanks for all your help, I'm learning here that Excel is much more capable than I previously thought. Whether I can now get internal sponsors to understand the power of what I'm compiling remains to be seen...!

Presumably now the macro has been run to create what I need, I don't need to save as a macro-enabled worksheet as it doesn't need to be dynamic going forward?

Thanks again

RA

 

[MickG]

You're welcome
NB:- If you've placed the code in a new workbook , then you need to Save as "xlsm" to save the code, otherwise no.