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

In article <09ff74fc-d6b6-4ed5-b919-(E-Mail Removed)
s.com>, Seanie <(E-Mail Removed)> writes
>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

--
Kevin