VBA Loop for data transfer

[Broga1981]

Hi guys, very new to VBA so please go easy on me as I am a complete beginner (just managed to work out how to transfer from one sheet to another).

One of the reports I run has a messed up format which puts staff names in column A but at every 5 rows (it includes other data in the other rows but not names).
What is the simplest way to transfer the names at these intervals A1, A6, A11, A16 etc to another sheet and also have it be dynamic.

This may be beyond my skill set but I have identified the need to try and move it around. Any help and simple explanations will be appreciated. Thank you.

 

[Broga1981]

There is 100% an easier way than doing it like this, i just dont know how haha

Worksheets("RAW").Range("A1").Copy Worksheets("Master").Range("A2")
Worksheets("RAW").Range("A6").Copy Worksheets("Master").Range("A3")
Worksheets("RAW").Range("A11").Copy Worksheets("Master").Range("A4")

 

[Joe4]

Try something like this:

    Dim lr As Long
    Dim r As Long
  
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
  
'   Loop through all rows in column A starting at row 1 and going every 5 rows
    For r = 1 To lr Step 5
'       Copy value from column A
        Cells(r, "A").Copy ...
    Next r

obviously, you would need to complete the paste portion of the copy command, but this gives you the structure.

 

[Broga1981]

Joe thats great thank you

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Broga1981]

You have helped greatly, and there is enough left for me to learn on my own also. Perfect

 

[Joe4]

You have helped greatly, and there is enough left for me to learn on my own also. Perfect

Excellent!
Feel free to post back if you run into any issues getting it to work the way you need.