Automate select, copy, paste command with VBA

Hello Members and Experts,

Cross-posted at https://www.mrexcel.com/board/thread...-loop.1167869/ (note that an answer has been posted there) --6SJ

I need to efficiently record a list of 80 bowlers for further analysis and their scores and hope to obtain your help. The table only contains 4 columns:

column A: Member's Number
column B: Member's Name
column C: Rank
column D: Score

The most headache is the fact that not all bowlers play exactly same number of game; some play more, some play less, so each member will occupy different number of rows for each score. Eg. the 1st bowler plays 4 games, 2nd bowler plays 6 games, and the 3rd bowler plays 3 games, and so forth. Thus column A and B, starting from row 2 for the first bowler, contain empty rows until the next bowler appears. I then have to copy each bowler's number (column A) and name (column B) and paste to every single row of game score.

With my very limited knowledge on VBA/Macro I can only create below one that can accomplish the first bowler, but without being able to repeat the process to all others.

Please Login or Register  to view this content.

I realize some loop process with the likes of Do...Until, Do...While, For....Next but just don't know the appropriate syntax.

Thanks very much in advance.

Please Login or Register  to view this content.

What analysis is required. Do you want to calculate the average of scores per match?
Formula for average in E2 then copy down

=IF($B2="","",AVERAGE($D2:INDEX($D3:$D$18,IFERROR(MATCH("*",$B3:$B$18,0)-1,ROW($B$18)-ROW()))))

Because this is your first post, I suggest you take the time to review our rules. There aren't many, and they are all important.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added the link since this is your first post.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have added the code tags since this is your first post.

Thank you so much to AlphaFrog, that works after adjusting "A1:B" to "A2:B".

To Torachan,
thank you for your help anyway but that doesn't work.