March 24, 2021

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

3 thoughts 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

  2. Hello Sir,
    I was going through your videos and they are excellent for new learner but I am not able to apply logic in my problem. It will be great if you can help me in fixing my problem.

    My Problem is: Copying and replicate the same data from one sheet to another till nth row
    In Detail –
    Brand, sub_brand, start_date, end_date has to keep in one sheet which is only one entry at a time and these variables has to be in columns one after one in sheet 1

    Sheet 2 needs transpose of brand, sub_brand, start_date, end_date as header and copying data from sheet 1 to sheet 2 till nth time and the data to copy is same till nth row and after these need to do calculation as concatenate start_date and end_date to create new variable and then find difference of date.

    It will be very helpful if you give some ideas to solve the problem

  3. Dear Sir,
    Myself, Jeethu from Qatar. I work for a small private firm here. However, my work has turned out to be very tedious
    May i kindly request if there is some project we could work on?

    Enclosed is a file and i have marked my doubt in 2nd sheet (“Master”),requesting your advise on how to insert a VBA to execute the conditional formatting for duplicates and take the odd one out.
    I have sent you email from [email protected] today.

    Kindly let me know, Sir


Comments are closed.