transpose fx using VB

C

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)))
 
D

David McRitchie

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

crapit

value of lastrow is dynamic but will never 100
David McRitchie said:
what is the value of lastrow, there are only 256 columns
in Excel. See "Specification limits" in Excel Help (not VBA Help).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

crapit said:
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)))
Click to expand...
Click to expand...
 
D

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
 
Top