Delete all Links via VBA

[Seanie]

How can I delete all links/range names within a range of sheets? I
extract and Copy Paste Special values via below, but it retains links
to the source document (which I don't need)

Thanks

Sourcewb.Sheets(Array("Report1", "Report2", "Report3")).Copy
Set Destwb = ActiveWorkbook

For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh

 

[Kevin Vivian]

How can I delete all links/range names within a range of sheets? I
extract and Copy Paste Special values via below, but it retains links
to the source document (which I don't need)

Thanks

Sourcewb.Sheets(Array("Report1", "Report2", "Report3")).Copy
Set Destwb = ActiveWorkbook

For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh

Instead of the line -

".Cells.PasteSpecial xlPasteValues"

try this -

..Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

It worked for me on a similar project.

Cheers