|
|
|
|
|
|
|
|
|
Excel
|
Delete Hyperlinks, Links, Formulas From Workbook
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
johnske
|
|
Description:
|
On running the code, all hyperlinks, internal and external links, and formulae are deleted from the workbook, but all values are retained.
|
|
Discussion:
|
You may have used all sorts of links and formulae to collate data in a workbook but, once collated, all these links etc. are no longer required. Alternatively, you may wish to forward the workbook with the collected data to someone but you don't want them to have access to your formulae or to have them getting that annoying "Update Links?" message every time they open the workbook. Should you also wish to remove all VBA code, see this entry http://www.vbaexpress.com/kb/getarticle.php?kb_id=93
|
|
Code:
|
instructions for use
|
Option Explicit
Sub DeleteLinksInBook()
Dim Ws As Worksheet, FirstSheet As Worksheet
Dim Query As VbMsgBoxResult
Query = MsgBox("CAUTION: Every single link, formula " & _
"& hyperlink in this " & vbLf & _
"book will be deleted - Do you " & _
"still want to proceed?", vbYesNo, _
"Sever All Links?")
If Query = vbNo Then Exit Sub
Set FirstSheet = ActiveSheet
Application.ScreenUpdating = False
For Each Ws In Worksheets
Ws.Activate
With Cells
.Select
.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With
Cells.Hyperlinks.Delete
[A1].Select
Next
FirstSheet.Activate
End Sub
|
|
How to use:
|
- Open an Excel workbook
- Select Tools/Macro/Visual Basic Editor
- In the VBE window, select Insert/Module
- Copy and paste the code into the Module
- Now select File/Close and Return To Microsoft Excel
- Save your work
|
|
Test the code:
|
- Select Tools/Macro/Macros.../DeleteLinksInBook/Run
- Select the various parts that were linked to see that all links are severed
- To apply this to just one sheet, you need to look at the allied knowledge-base entry http://www.vbaexpress.com/kb/getarticle.php?kb_id=377
|
|
Sample File:
|
DeleteLinks_Book.zip 13.33KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 140 times.
|
|
|