copy transpose and paste error!?

D

Darius

I am trying to transpose and copy data from sheet1! from c8..f8; C144..F144,
C277..F277, C410..F410, C545..F545, C680..F680, C815..F815

to:

Sheet2
G20..G23, G24..G27, ....till end.

I am using the below sub:
Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Originrow = 8
Destcol = 7
Destrow = 20
For Originrow = 8 To 818 Step 135
Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy _
Destination:=Sheet2!Cells(Destcol, Destrow)
Destrow = Destrow + 1
Next
End Sub

I am getting the error [runtime error 438 object doesnot support this
property or method] Could you help please?
 
D

Darius

Hi there;
Using the archive of posts here I came up with below, it comes out with no
error but nothing happens. Can anybody please help, I hardly know any
programing. Appreciate your help.

Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Dim ws2 As Worksheet
Set ws2 = Sheets("Sheet2")

Originrow = 8
Destcol = 7
Destrow = 20

For Originrow = 8 To 818 Step 135

Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy
ws2.Cells(Destcol, Destrow + 1)
Originrow = Originrow + 1
Next
End Sub
 
D

Dave Peterson

Don't you have a problem with the step, too? 8 to 144 isn't 135.

Dim DestCell as range
dim Originrow As Long
dim i As Long

set destcell = range("G20")

For Originrow = 8 To 818 Step 135
Cells(Originrow, 2).resize(1,4).Copy
destcell.pastespecial transpose:=true
set destcell = destcell.offset(0,1)
Next originrow

Sometimes, this syntax:
Cells(Originrow, 2).resize(1,4).Copy _
is easier to read than:
Range(Cells(Originrow, 2), Cells(Originrow, 6)).Copy _

And why did you change that to "originrow + 3"? That doesn't match your
description.

I am trying to transpose and copy data from sheet1! from c8..f8; C144..F144,
C277..F277, C410..F410, C545..F545, C680..F680, C815..F815

to:

Sheet2
G20..G23, G24..G27, ....till end.

I am using the below sub:
Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Originrow = 8
Destcol = 7
Destrow = 20
For Originrow = 8 To 818 Step 135
Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy _
Destination:=Sheet2!Cells(Destcol, Destrow)
Destrow = Destrow + 1
Next
End Sub

I am getting the error [runtime error 438 object doesnot support this
property or method] Could you help please?
Click to expand...
 
D

Darius

Dave;
Thank you so much for your help.
Still has 2 problems, it paste the data in the same sheet1 and not sheet 2.
And also the pasted data supposed to come in one coulmn (Col G in sheet2)
start from G20....G..
Appreciate your help

Dave Peterson said:
Don't you have a problem with the step, too? 8 to 144 isn't 135.

Dim DestCell as range
dim Originrow As Long
dim i As Long

set destcell = range("G20")

For Originrow = 8 To 818 Step 135
Cells(Originrow, 2).resize(1,4).Copy
destcell.pastespecial transpose:=true
set destcell = destcell.offset(0,1)
Next originrow

Sometimes, this syntax:
Cells(Originrow, 2).resize(1,4).Copy _
is easier to read than:
Range(Cells(Originrow, 2), Cells(Originrow, 6)).Copy _

And why did you change that to "originrow + 3"? That doesn't match your
description.

I am trying to transpose and copy data from sheet1! from c8..f8; C144..F144,
C277..F277, C410..F410, C545..F545, C680..F680, C815..F815

to:

Sheet2
G20..G23, G24..G27, ....till end.

I am using the below sub:
Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Originrow = 8
Destcol = 7
Destrow = 20
For Originrow = 8 To 818 Step 135
Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy _
Destination:=Sheet2!Cells(Destcol, Destrow)
Destrow = Destrow + 1
Next
End Sub

I am getting the error [runtime error 438 object doesnot support this
property or method] Could you help please?
Click to expand...
Click to expand...
 
D

Dave Peterson

I'm confused about your code and your note.

You say C:F should be copied. But your code starts in column 2 (B).

I used column 2 (B).

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim OriginRow As Long

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

With Worksheets("Sheet1")
For OriginRow = 8 To 818 Step 135
.Cells(OriginRow, 2).Resize(1, 4).Copy
DestCell.PasteSpecial Transpose:=True
Set DestCell = DestCell.Offset(4, 0)
Next OriginRow
End With

End Sub




Dave;
Thank you so much for your help.
Still has 2 problems, it paste the data in the same sheet1 and not sheet 2.
And also the pasted data supposed to come in one coulmn (Col G in sheet2)
start from G20....G..
Appreciate your help

Dave Peterson said:
Don't you have a problem with the step, too? 8 to 144 isn't 135.

Dim DestCell as range
dim Originrow As Long
dim i As Long

set destcell = range("G20")

For Originrow = 8 To 818 Step 135
Cells(Originrow, 2).resize(1,4).Copy
destcell.pastespecial transpose:=true
set destcell = destcell.offset(0,1)
Next originrow

Sometimes, this syntax:
Cells(Originrow, 2).resize(1,4).Copy _
is easier to read than:
Range(Cells(Originrow, 2), Cells(Originrow, 6)).Copy _

And why did you change that to "originrow + 3"? That doesn't match your
description.

I am trying to transpose and copy data from sheet1! from c8..f8; C144..F144,
C277..F277, C410..F410, C545..F545, C680..F680, C815..F815

to:

Sheet2
G20..G23, G24..G27, ....till end.

I am using the below sub:
Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Originrow = 8
Destcol = 7
Destrow = 20
For Originrow = 8 To 818 Step 135
Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy _
Destination:=Sheet2!Cells(Destcol, Destrow)
Destrow = Destrow + 1
Next
End Sub

I am getting the error [runtime error 438 object doesnot support this
property or method] Could you help please?
Click to expand...
Click to expand...
Click to expand...
 
D

Darius

Perfect.
Thanks. I changed the column number to 3 by the way.

Dave Peterson said:
I'm confused about your code and your note.

You say C:F should be copied. But your code starts in column 2 (B).

I used column 2 (B).

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim OriginRow As Long

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

With Worksheets("Sheet1")
For OriginRow = 8 To 818 Step 135
.Cells(OriginRow, 2).Resize(1, 4).Copy
DestCell.PasteSpecial Transpose:=True
Set DestCell = DestCell.Offset(4, 0)
Next OriginRow
End With

End Sub




Dave;
Thank you so much for your help.
Still has 2 problems, it paste the data in the same sheet1 and not sheet 2.
And also the pasted data supposed to come in one coulmn (Col G in sheet2)
start from G20....G..
Appreciate your help

Dave Peterson said:
Don't you have a problem with the step, too? 8 to 144 isn't 135.

Dim DestCell as range
dim Originrow As Long
dim i As Long

set destcell = range("G20")

For Originrow = 8 To 818 Step 135
Cells(Originrow, 2).resize(1,4).Copy
destcell.pastespecial transpose:=true
set destcell = destcell.offset(0,1)
Next originrow

Sometimes, this syntax:
Cells(Originrow, 2).resize(1,4).Copy _
is easier to read than:
Range(Cells(Originrow, 2), Cells(Originrow, 6)).Copy _

And why did you change that to "originrow + 3"? That doesn't match your
description.


Darius wrote:

I am trying to transpose and copy data from sheet1! from c8..f8; C144..F144,
C277..F277, C410..F410, C545..F545, C680..F680, C815..F815

to:

Sheet2
G20..G23, G24..G27, ....till end.

I am using the below sub:
Sub test()

Dim Destcol As Long, Originrow As Long, Destrow As Long, i As Long
Originrow = 8
Destcol = 7
Destrow = 20
For Originrow = 8 To 818 Step 135
Range(Cells(Originrow, 2), Cells(Originrow + 3, 6)).Copy _
Destination:=Sheet2!Cells(Destcol, Destrow)
Destrow = Destrow + 1
Next
End Sub

I am getting the error [runtime error 438 object doesnot support this
property or method] Could you help please?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

copy transpose function? 1
Eliminate blanks while copy row and transpose 11
copy/paste in the next column 4
Copy and Paste Special #Value Error 3
Find Copy and Paste 4
Copy data from textfile, paste and transpose to sheet2 1
copy and paste 8
VBA Copy, Paste and Format 2

Top