如何使用VBA转置链接

Question

，假设我在一张纸上有一行：

A1 B1 C1 D1 E1

我想复制，转置和粘贴链接到这些单元格的excel中的另一张纸：

=Sheet2!A1
=Sheet2!B1
=Sheet2!C1
=Sheet2!D1
=Sheet2!E1

有没有办法使用VBA做到这一点？由于某种原因，此代码不起作用，我得到的只是

=Sheet2!A1 
=Sheet2!A2

等。

Sub Pastelinks()
Worksheets("Data_All_01").Range("$D$23:$AG$23").Copy
With Worksheets("DB 1,5€").Range("$F$287")
    .PasteSpecial Transpose:=True
    .Activate
    ActiveSheet.Paste Link:=True
End With
End Sub

Answer 1

避免在VBA中复制/粘贴

Sub test()
    Dim i As Integer, ii As Integer
    Dim ws As Worksheet
    Set ws = Worksheets("DB 1,5") 'insert euro character back
    '4 is column D
    '33 is column AG
    For i = 4 To 33
        ws.Cells(287 + ii, 6).FormulaR1C1 = "=Data_All_01!R23C" & i
        ii = ii + 1
    Next
End Sub

Answer 2

这将使用打开的工作表的范围A1：A4的值，并使用初始范围的启动列和行将它们放入表（2）中。

Option Explicit
Sub TransposeMe()
    Dim my_cell         As Range
    Dim l_counter       As Long
    Dim l_row           As Long
    Dim l_col           As Long
    l_row = Range("A1:D1").Row
    l_col = Range("A1:D1").Column
    For Each my_cell In Range("A1:D1")
        Sheets(2).Cells(l_row, l_col) = my_cell
        l_row = l_row + 1
    Next my_cell
End Sub