Copy 40 Rows and move to next column

[Nandan]

Hi

I have 5000 rows in Column A I want to copy only 1 to 40 rows and paste ti Column B and next 41-80 rows and copy to column C like goes on rows 5000

How to write Macro for that in excel



Thanks & Regads

Nandan

 

[ogo]

Hi Nandan, try this,

Sub TryThis()
Dim MyCol
MyCol = 2               'Paste to column B first
For iR = 1 To 125       '5000/40
    For i = 1 To 40
        Cells(i, 1).Select
        Selection.Cut
        Cells(i, MyCol).Select
        ActiveSheet.Paste
    Next
        Range("A1:A40").Select
        Selection.Delete Shift:=xlUp
        MyCol = MyCol + 1
Next
End Sub

Thanks,
Ogo

 

[Nandan]

Thank u Very much Ogo. Its work perfectly

Thanks & Regards

Nandan