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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

One thought on “Copy Every Nth Row of Data From Sheet1 to Sheet2”

  1. I think it would be much more economic to use the loop to create a string of the range to copy and then copy it to the other worksheet in one move:

    Option Explicit

    Sub CopyEveryNthRow()
    Dim Rng1 As Range, Rng2 As Range
    Dim Str As String
    Dim i As Long, n As Long, StartRowSheet2 As Long, CountRows As Long
    StartRowSheet2 = 1
    n = 5
    Set Rng1 = Range(“A1”).CurrentRegion
    CountRows = Rng1.Rows.Count
    Str = “1:1”
    For i = 1 To CountRows – 1 Step n
    Str = Str & “,” & i + n & “:” & i + n
    Next i
    ‘Str = “1:1, 6:6, 11:11”
    Set Rng2 = Range(Str)
    Rng2.Copy Worksheets(2).Cells(StartRowSheet2, 1)
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.