Copy Every Nth Row of Data From Sheet1 to Sheet2

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

Automatically Copy Every Nth Row of Data From Sheet1 to Sheet2

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

Copy Every Nth Row of Data From Sheet1 to Sheet2

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

IF-MOD-Functions to Locate Every 5th Row
IF-Function to locate every 5th row

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