Delete all Links via VBA

  • Thread starter Thread starter Seanie
  • Start date Start date
S

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
 
s.com> said:
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
Click to expand...


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
 
Back
Top