Quick Fix to Sever Links on a Copied WB/WS
Posted on 2012-03-23
I have a nice macro that Kyle (Kgerb) and Dave (Dmille) wrote for me that reproduces a WS/WB from a master WB and puts it on the desktop. The challenge I'm having is that when I go to open the new WB it asks if I want to update links. As far as I can tell, I have no links (range name references, formulas, etc.) in the copied WS.
Is there a line of code that I can add that will automatically sever any links?
Here is the code.
Dim sWBName As String, sWSName As String, bViz As Boolean, bProtect As Boolean
sWBName = "NewWorkbook" 'Add path if you wish (e.g. C:\My folder\NewWorkbook)
sWSName = "NewWorksheet"
bViz = .Visible
.Visible = True
.Visible = bViz
bProtect = .ProtectContents
.Name = sWSName
Application.CutCopyMode = False
If bProtect Then .Protect
ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlNormal