creating macro's Closed

Question

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.


 

Configuration: Windows XP / Firefox 3.0.15

venkat1926 · Answer

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

Creating macro's

1 response

Similar discussions

Subscribe To Our Newsletter!