transpose fx using VB

[crapit]

Got this error
Run -time error 1004:
Application -defined or object -defined error

Public lastrow As Long
lastrow = Worksheets("Spare").Cells(Rows.Count, 1).End(xlUp).row

The highlighted statement below: -

Worksheets("test").Range(Cells(1, 1), Cells(6, lastrow)).Value =
WorksheetFunction.Transpose(Worksheets("Spare").Range(Cells(1, 1),
Cells(lastrow, 6)))

 

[David McRitchie]

what is the value of lastrow, there are only 256 columns
in Excel. See "Specification limits" in Excel Help (not VBA Help).

 

[crapit]

value of lastrow is dynamic but will never 100

what is the value of lastrow, there are only 256 columns
in Excel. See "Specification limits" in Excel Help (not VBA Help).

 

[David McRitchie]

Looks like you only want six rows when finished, which
would be the first six column in the original.

Normally in pasting or transposing you would only select
a single cell as the destination and let it assume the dimension
from the source.

Sub spare_to_test()
Dim lastrow As Long
lastrow = Worksheets("Spare").Cells(Rows.Count, 1).End(xlUp).row
Worksheets("test").Cells.Clear 'clear out previous content
Worksheets("spare").Range(Cells(1, 1), Cells(lastrow, 6)).Copy
Worksheets("test").Range("A1").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False 'clear copymode
End Sub