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)

	A	B	C
1	Name
2	Address
3	Telephone
4	Mobile

<tbody>
</tbody>

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

	A	B	C
1
2		Name
3
4
5
6		Address
7
8
9
10		Telephone
11
12
13
14		Mobile

<tbody>
</tbody>

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