Previous Topic Next Topic

Creating macro's

Closed
Deepu - Aug 30, 2010 at 08:22 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 30, 2010 at 09:04 PM
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 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 30, 2010 at 09:04 PM
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
0