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
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
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
Regards
Jeethu
+974-33062678