Facing some diffculty in achieving the desired result for my macro.



Intention: I have a list of data in sheets(inputs).column BN(the number of rows that has value will vary and hence i created a loop that will run the macro until the active cell is blank). My macro starts from Range(BN2) and stretches all the way down column BN, it stops only when it hits a blank row
Desired result for the macro will be to start copying the cell value in sheet(input).Range(BN2) paste it to sheet(Output).Range(A2:A10).
For example, if "Peter" was the value in cell sheet(input),range(BN2) then when the marco runs and paste the value into sheet(Output) range(A2:A10). ie range A2:A10 will reflect "Peter"
Then the macros loop back to sheet(inputs) & copy the next cell value and paste it to range(A11:A19) Example: "Dave" was the value in sheet(inputs) Range(BN3), then "Dave" will be paste into the next 9 rows in sheet(mywork).Range(A11:A19). A11:A19 will reflect "Dave"
Again repeating the same process goes back to sheet(input) this time range(BN4), copys the value goes to sheet(Output) and paste it into A20:A29.
Basically the process repeats....
The macro ends the when the activecell in sheet(input) column A is empty. Urgently seek the advice/assistance from the experts in the forumn. Thanks in advance.

Your task description is ambiguous. For example, it's by no means clear what goes into A2:A10. Does each cell in A2:A10 get populated with 'Peter', or something else? If it's something else, where does the something else originate? And why do there appear to be 9 rows per person (Peter = A2:A10; Dave = A11:A19)? Do all entries have 9 rows, or only some? If only some, what are the criteria?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]