transpose fx using VB

  • Thread starter Thread starter crapit
  • Start date Start date
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)))
 
what is the value of lastrow, there are only 256 columns
in Excel. See "Specification limits" in Excel Help (not VBA Help).
 
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).
Click to expand...
 
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
 
Back
Top