How to paste a link to a worksheet?

user_14893651 0
Viewed 62

All of the variables are defined correctly. This works till it gets to the final statement shown and then it says

"Paste method of Worksheet class failed".

Set TargetSheet = ActiveSheet
For Each cell In TargetSheet.Columns(1).Cells
    If IsEmpty(cell) = True Then FirstRow = cell.Row: Exit For
Next cell
LastRow = FirstRow + 6

Sheets(ClientName).Range("A3:Q9").Copy

With Sheets("4 Policies")
    .Activate
    .Range("A" & FirstRow).Select
    ActiveSheet.Paste Link:=True
End With
paste hyperlink vba excel
1 Answers

Try this

Set TargetSheet = ActiveSheet
For Each cell In TargetSheet.Columns(1).Cells
  If IsEmpty(cell) = True Then FirstRow = cell.Row: Exit For
Next cell
LastRow = FirstRow + 6

Sheets("4 Policies").Range("A" & FirstRow).Resize(7, 17).Formula = "=" & ClientName & "!A3"

If you trying to find the cell below the last used one in column A, your code becomes

FirstRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("4 Policies").Range("A" & FirstRow).Resize(7, 17).Formula = "=" & ClientName & "!A3"

or, if the active sheet is "4 Policies"

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(7, 17).Formula = "=" & ClientName & "!A3"
user_133864200
Related