hyperlink - 不带公式但带链接的选择性粘贴

Question

我尝试根据条件将行列表从一张表传递到另一张表，但没有公式和格式，除了一列有指向另一个文档的链接并且链接也需要复制。知道如何使用 ActiveSheet.Paste Special 忽略公式但粘贴链接吗？谢谢（见下面的代码）

Sub Button3_Click()
a = Worksheets("Target List").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a

If Worksheets("Target List").Cells(i, 17).Value = "Yes" Then
Worksheets("Target List").Rows(i).Copy
Worksheets("Completed List").Activate

b = Worksheets("Completed List").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Completed List").Cells(b + 1, 1).Select
ActiveSheet.PasteSpecial Format:=3, Link:=True

Worksheets("Target List").Activate

End If


Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Target List").Cells(1, 1).Select
ActiveSheet.Range("Q8:R" & Range("Q8").End(xlDown).Row).ClearContents


End Sub

标签： hyperlinkpaste

Answer 1

对于那些正在寻找答案的人。我通过先粘贴值然后分别粘贴唯一链接来提供自己的解决方案，它可以工作。见下文，您可以根据自己的目的进行调整

Sub Button3_Click()
Dim HL As Hyperlink
a = Worksheets("Target List").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To a

If Worksheets("Target List").Cells(i, 17).Value = "Yes" Then
Worksheets("Target List").Rows(i).Copy
Worksheets("Completed List").Activate

b = Worksheets("Completed List").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Completed List").Cells(b + 1, 1).Select
ActiveSheet.PasteSpecial Format:=3, Link:=True

Worksheets("Target List").Activate
Worksheets("Target List").Cells(i, 19).Copy
Worksheets("Completed List").Activate
Worksheets("Completed List").Cells(b + 1, 19).Select
ActiveSheet.PasteSpecial Link:=True


Worksheets("Target List").Activate

End If


Next


Application.CutCopyMode = False

ThisWorkbook.Worksheets("Target List").Cells(1, 1).Select
ActiveSheet.Range("Q8:U" & Range("Q8").End(xlDown).Row).ClearContents


End Sub