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