Excel VBA: Looping through rows and copy cell values to another worksheet

[Swagata]

Facing some diffculty in achieving the desired result for my macro. Intention: I have a list of data in sheets(input).column A(the number of rows that has value will vary and hence i created a loop that will run the macro until the activecell is blank). My macro starts from Range(A2) and stretches all the way down column A, 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(A2) paste it to sheet(mywork).Range(B2:B6).

For example, if "Peter" was the value in cell sheet(input),range(A2) then when the marco runs and paste the value into sheet(mywork) range(B2:B6). ie range B2:B6 will reflect "Peter"

Then the macros loop back to sheet(input) & copy the next cell value and paste it to range(B7:B10) Example: "Dave" was the value in sheet(input) Range(A3), then "Dave" will be paste into the next 4 rows in sheet(mywork).Range(B7:B10). B7:B10 will reflect "Dave"

Again repeating the same process goes back to sheet(input) this time range(A4), copys the value goes to sheet(mywork) and paste it into B11:B15.

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. Cheers )))

Sub playmacro()
Dim xxx As Long, yyy As Long
ThisWorkbook.Sheets("Input").Range("A2").Activate
Do While ActiveCell.Value <> ""
DoEvents
ActiveCell.Copy
For xxx = 2 To 350 Step 4
yyy = xxx + 3
Worksheets("mywork").Activate
With ActiveSheet
.Range(Cells(xxx, 2), Cells(yyy, 2)).PasteSpecial xlPasteValues
End With
Next xxx
ThisWorkbook.Sheets("Input").Select
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True
End Sub

[ragulduy]

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Please adhere to Forum Rule No. 3 when posting
"3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing."

Another:

Please Login or Register  to view this content.

[Swagata]

Thanks a lot! I got my solution!

@John - Will follow from next time.

[R.a.Malpedi]

Hi I have same problem. I want to copy each code & past it to another sheet's one particular cell. Want to continue this process by pasting 1 by 1 code in another worksheet. Please help me on this