vba to paste special - transpose

[Smithsat34]

Hi Board,

Am a little out of practise with VBA. I'm getting a 438 error on the macro below.
I'm trying to copy the content of column A, in blocks of 5 rows and transpose into 5 columns on sheet 3.


Sub Macro4()

Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim lastRow As Long

x = 1
y = 5
z = 1

lastRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row


For A = 1 To lastRow


Worksheets("Sheet2").Range("A" & x & ":A" & y).Copy

Worksheets("Sheet3").Range("A" & z).Selection.PasteSpecial Transpose:=True


x = x + 5
y = y + 5
z = z + 1

Next A
End Sub

The error message appears at the paste line.

Thanks in advance

bob

 

[Fluff]

Remove the word "Selection"

 

[Smithsat34]

Fluff,

Thank you,

could you explain what Selection was actually doing to make the error?

bob

 

[Fluff]

Selection is the selected cells on the activesheet & you use it like

Selection.Copy

You cannot use it in the manner you tried

 

[Smithsat34]

thanks again..

 

[Fluff]

You're welcome

 

[My Aswer Is This]

You may want to try this:

Sub New_One()
'Modified 5/8/18 10:35 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
b = 1
Dim lastRow As Long
lastRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lastRow Step 5
        Sheets(2).Cells(i, 1).Resize(5).Copy
        Sheets(3).Cells(b, 1).PasteSpecial Transpose:=True
        b = b + 1
    Next
Application.ScreenUpdating = True
End Sub