Loop columns copy/paste

[Keala]

My question is probably very easy for someone who knows VBA more then me.

I have *.xls file with five columns each column is x rows (let say 40000 rows ). I need to copy 200 rows from column D sheet 1 to sheet 2 column B. Then copy next 200 rows from sheet 1 and paste it in sheet 2 column C and so on until last row.

I can record below way but to copy paste for each 200 is not a good way. I think a loop or a for would be much better, but can't make it my self.

Any suggestions or link to a similar problem with solution is appreciated.

Thank you,

Sheets("Sheet1").Select
    Range("D46:D246").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B3").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("D247:D447").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("c3").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1").Select
    Range("D448:D648").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("d3").Select
    ActiveSheet.Paste

 

[Fluff]

How about

Sub Keala()
   Dim i As Long, x As Long
   
   x = 1
   With Sheets("Sheet1")
      For i = 46 To .Range("D" & Rows.Count).End(xlUp).Row Step 200
         x = x + 1
         .Cells(i, 4).Resize(200).Copy Sheets("Sheet2").Cells(3, x)
      Next i
   End With
End Sub

 

[Keala]

Thank you Fluff, very much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback