VBA: Copy column data and paste with every nth rows

[epano]

Hello. Could someone help me out with a vba code. I am currently learning vba but i cannot figure out how to code this issue.

I am trying to copy data in a column on one sheet (sheet1), then paste it on another sheet (sheet2), but this time, evenly spaced out every 9 rows. The number of data rows could differ each time so it has to be able to find the last row with data. Also, in sheet 2, where I paste the first data cell is not the same as in sheet1. I have provided an example below to make it easier to understand.

Thanks!

Current (Sheet 1)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Address[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Telephone[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Mobile[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Need (Sheet 2) - i used less rows in between data here but for the actual issue i need data to be placed every 9th row [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD]Address[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD]Telephone[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD][/TD]
[TD]Mobile[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Note: the letters and numbers are column and row identifiers (A1, B2, etc.). Not column headers. There are no column headers in the actual file.

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub CopySkipRws()
   Dim i As Long, r As Long
   Dim Rng As Range
   
   With Sheets("sheet1")
      Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
   End With
   For i = 2 To Rng.Count * 9 Step 9
      r = r + 1
      Sheets("Sheet2").Range("B" & i).Value = Rng(r).Value
   Next i
End Sub

 

[epano]

That worked perfectly! Thank you!

 

[Fluff]

You're welcome

 

[evee]

Hi are you able to copy over the formatting as well? e.g. i want the font to be bold.
and are you able to copy and paste to merge cells with skprws?

 

[Fluff]

Hi & welcome to MrExcel.
Please start a thread of your own for this question.Thanks