Tannspose and cycle
 

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

Tannspose and cycle
 

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

sebastico wrote:

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

--

Dave Peterson

Tannspose and cycle
 

Dave Excelent

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

Thank you very much indeed

"sebastico" wrote:

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

Tannspose and cycle
 

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.

"sebastico" wrote:

Dave Excelent

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

Thank you very much indeed

"sebastico" wrote:

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

Tannspose and cycle
 

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.



sebastico wrote:

Dave Excelent

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

Thank you very much indeed

"sebastico" wrote:

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


--

Dave Peterson