Copy Every n1 th row to sheet2 from row x1 to row x2 (sheet1), then copy Every n2 th row to sheet 2 right after every n th row data pasted

2
votes

I have an excel file with 2000 rows and 50 columns in sheet1. I need to copy and paste every other rows from Row 3 to Row 900 of sheet1 to sheet2 in same workbook. Then every 6th row from Row 901 to 2000 of sheet1 to sheet2 right after the rows pasted before. I am very new to VBA. Can anyone please help me with writing macro for this...

Trying:

    Dim strValue As String
    Dim strCellNum As String
    Dim x As String
    x = 1
    For i = 1 To 700 Step 7
    strCellNum = "A" & i
    strValue = Worksheets("Sheet1").Range(strCellNum).Value
    Debug.Print strValue
    Worksheets("Sheet2").Range("A" & x).Value = strValue
    x = x + 1
    Next
1
vbaexcel
For r = 3 to 900 Step 2....For r=901 to 2000 Step 6 – Tim Williams
For r = 3 to 899 Step 2....For r=901 to 1999 Step 6 ? – Fumu 7
How to set up the strigs for rows , I know if I want just values I can use this type of code Dim strValue As String Dim strCellNum As String Dim x As String x = 1 For i = 1 To 700 Step 7 strCellNum = "A" & i strValue = Worksheets("Sheet1").Range(strCellNum).Value Debug.Print strValue Worksheets("Sheet2").Range("A" & x).Value = strValue x = x + 1 Next But I don't know how to write macro for copying rows – Nipa
Don't add code in comments - edit your question to add it, and format it using the {} button. – Tim Williams

1 Answers

1
votes
Sub CopyNew()
    Dim NextDest As Long
    Dim CurRow As Long

    NextDest = 1

    For CurRow = 3 To 900 Step 2
        Sheets("Sheet1").Rows(CurRow).Copy
        Sheets("Sheet2").Range("A" & NextDest).PasteSpecial
        NextDest = NextDest + 1
    Next CurRow
    For CurRow = 901 To 2000 Step 6
        Sheets("Sheet1").Rows(CurRow).Copy
        Sheets("Sheet2").Range("A" & NextDest).PasteSpecial
        NextDest = NextDest + 1
    Next CurRow

End Sub