Excel VBA - 将范围复制转置粘贴

Question

我正在按照“PasteSpecial”帮助菜单的步骤操作，但是我的代码似乎总是出现错误。

我想要将Worksheets("Sheet1").Range("A1","A5")转置后粘贴到Worksheets("Sheet2").Range("A1","E1")中。

有什么最简单的方法可以实现这个目标吗？

- user1130306

---

我想建议您使用转置工作表方法而不是滥用剪贴板。 - binki

Answer 1

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

- GSerg

---

谢谢，我没有将pastspecial命令放在新行上。 - user1130306

我必须将转置粘贴到不同的单元格集中。因此，如果从A1:B5复制，则必须粘贴到A6。 - Orbiting Eden

Answer 2

以下是一种不使用剪贴板的高效选项。

Sub transposeAndPasteRow(rowToCopy As Range, pasteTarget As Range)
    pasteTarget.Resize(rowToCopy.Columns.Count) = Application.WorksheetFunction.Transpose(rowToCopy.Value)
End Sub

像这样使用它。

Sub test()
    Call transposeAndPasteRow(Worksheets("Sheet1").Range("A1:A5"), Worksheets("Sheet2").Range("A1"))
End Sub

- GollyJer

---

这是更好的方法。特别是如果你正在寻找高性能。感谢提供代码！ - Owennn

在所有的 Stack Exchange 回答中最被低估的答案。 - DeerSpotter

当我使用这段代码时，pasteTarget 限制了粘贴的单元格数量。例如，当复制此代码时，它只会将 Worksheets("Sheet1").Range("A1") 的值粘贴到 Worksheets("Sheet2").Range("A1")。但是，当我将代码修改为 Worksheets("Sheet1").Range("A1:E1") 时，它可以正常工作。显然，当要复制的单元格范围是动态的，并且如果粘贴目标太小，则不会复制所有内容，如果太大，则会在前导单元格中放置 #N/A，这是主要问题所在。 - Steven Pomponio

@StevenPomponio 粘贴目标只需要是您目标范围的第一个单元格。rowToCopy 是您动态范围需要应用的唯一位置。 - GollyJer

为了使它正常工作，我需要将“A1:A5”范围复制到“A1:E1”范围，所以我对你的第一个代码进行了修改，变成如下形式：pasteTarget.Resize(, rowToCopy.Rows.Count) = Application.WorksheetFunction.Transpose(rowToCopy.Value)，主要修改是Resize参数。 - Steven Pomponio

你创建了一个名为 transposeAndPasteColumn 的不同函数。 - GollyJer

Answer 3

WorksheetFunction Transpose()

使用Transpose选项进行复制、粘贴的替代方法是直接输入公式，可以简化操作。

    =TRANSPOSE(Sheet1!A1:A5)

或者如果您更喜欢VBA：

    Dim v
    v = WorksheetFunction.Transpose(Sheet1.Range("A1:A5"))
    Sheet2.Range("A1").Resize(1, UBound(v)) = v

注意: 您也可以使用后期绑定的Application.Transpose。

Microsoft 365帮助文档指出，如果您有当前版本的Microsoft 365，则可以直接在目标范围的左上角单元格中输入公式，否则必须通过Ctrl+Shift+Enter输入公式作为传统数组公式来确认它。

版本 Excel版本2007+，自2011年以来的Mac，适用于Microsoft 365

- T.M.