How to copy every nth row of data from one worksheet to another automatically using VBA. Watch the video below:

We have already learnt how to transfer specific data from one worksheet to another for reports. In another video we learnt about the different methods to transfer data from Excel worksheet with VBA.

Here is the complete code to copy specific rows of data from one worksheet to another:

Sub copyEveryNthRow()
Dim i As Long, n As Long, countrows As Long, startrowsheet2 As Long
Dim Rng As Range

startrowsheet2 = 1

n = 5

Set Rng = Sheet1.Range(“A1”).CurrentRegion

countrows = Rng.Rows.Count

‘MsgBox countrows

For i = 1 To countrows Step n
Rng.Rows(i).Copy Sheet2.Range(“A” & startrowsheet2)
startrowsheet2 = startrowsheet2 + 1

Next i

End Sub

We can also identify the nth rows using the MOD function as shown in the image below:

5 Different Methods To Transfer Data From One Excel Worksheet To Another