Creating macro's [Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

We have two excel sheets say sheet-1 and sheet-2. I have to copy particular cells say, from column 'A' first cell to column 'A' 25th cell. Then paste it in sheet 2 in transpose manner.
Next from sheet-1, it has to skip 5 cell of column 'A' and again it has to paste in Sheet-2.

This cycle should repeat for 30 time.

Note: I need minimal manual intervention for this process.
Sheet-1 Sheet-2
column A. Column B.... Column A. Column B. Column C......
Row1 25 Row1 25 65 12
Row2 65 Row2 98 45 52
Row3 12 Row3
.
.
Row25 45
.
.
.
Row30 98
Row31 45
Row32 52
.
.

This is what i needed at front end.. Please help me solve this.




1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
try this macro (check if necessary modify the line
j=j+30)


Sub test()
Dim r As Range, dest As Range, j As Integer
j = 1
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Do
Set r = Range(.Range("A" & j), .Range("A" & j + 24))
'MsgBox r.Address
r.Copy
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial Transpose:=True
End With
j = j + 30
If .Range("A" & j + 25) = "" Then Exit Do
Loop
End With
Worksheets("sheet2").Rows("1:1").Delete
End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!