Tannspose and cycle

S

sebastico

Hello
In Excel 2003 I have the sheet1 A2:02 to A832:0632
How can I write this code in a cycle

Range("A2:O2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Range("A3:O3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A19").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Range("A4:O4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A37").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True


And so on to A632:0632

Thank you
 
D

Dave Peterson

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim DestCell As Range
Dim iRow As Long

Set wks = Worksheets("Sheet1") ' or ActiveSheet

Set DestCell = Worksheets("Sheet2").Range("a1")

With wks
For iRow = 2 To 632
' or last used cell in column A
'for irow = 2 to .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(iRow, "A").Resize(1, .Range("o1").Column).Copy
DestCell.PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Set DestCell = DestCell.Offset(18, 0)
Next iRow
End With
End Sub
 
S

sebastico

Dave Excelent

One more question. How can I do If the Renges are different?

Thank you very much indeed
 
J

Joel

Can you be more specifc. The VBA macro code works the same as the wroksheet
when it conmes to copying ranges. whatever you can do (or can't do) on a
worksheet also applies to the macro code.
 
D

Dave Peterson

It would depend on how they were different--and what range you're asking about
(the copied range or the destination cell????).

If there was no pattern, but the range addresses were always the same, you could
create an array of addresses to copy from -- and even an array of addresses that
you could paste into.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top