Solved

Quick Fix to Sever Links on a Copied WB/WS

Posted on 2012-03-23
36
368 Views
Last Modified: 2012-03-24
EE Professionals,

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.




Sub ExportSheetOnlyValues()
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"
With Sheets("Sheet1")
    bViz = .Visible
    .Visible = True
    .Copy
    .Visible = bViz
End With
With ActiveSheet
    bProtect = .ProtectContents
    .Unprotect
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues
    .Name = sWSName
    .Range("A1").Select
    Application.CutCopyMode = False
    If bProtect Then .Protect
End With
ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlNormal
ThisWorkbook.Activate
End Sub

Thank you,

B.
0
Comment
Question by:Bright01
  • 19
  • 17
36 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37758860
Bright - please attach the workbook prior to running this macro.  Or the workbook created to start.

Dave
0
 

Author Comment

by:Bright01
ID: 37758877
Dave,

I can't.  It contains a significant amount of Company Confidential information and would take an hr. to "clense".  Best I could do was mock up a sample and send.  That's why I thought there might be a quick fix by having the macro respond to any "link" issues.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37758912
Ok - pull up your new workbook.  Are you getting an update links prompt?

Hit Alt-E-K and printscreen what it says, if it does?

Go to range name manager and check as well.

Check any charts by clicking in there and looking at the series - any references to external files in the series formulas?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37758927
The problem is the worksheet that was copied must have had linkages with the original workbook where it came from.  Either a formula, a range name that was at the workbook scope, or a chart...

Bright - run this code in the offending workbook and advise if it prompts you with anything:
Sub showLinks()
Dim aLinks As Variant
Dim oldLink As String
Dim newLink As String
Dim i As Long
Dim myWkb As Workbook

    Set myWkb = ThisWorkbook
    aLinks = myWkb.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            MsgBox "Link: " & i & " => " & aLinks(i)
        Next i
    End If
End Sub

Open in new window

0
 

Author Comment

by:Bright01
ID: 37758958
When I run the macro it returns;

Link1 and then the path to the WorkBook I copied from.

There are no graphics on the WS.

There are a lot of Range Names in the original WB; but nothing referring specifically to the WS being copied.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37758977
Ok - then you have a link back to the original workbook.  That, hopefully, should make this more easy.

select all tabs in the new workbook.

Do a find on that link (just the filename of the original workbook should suffice.  Look in Formulas and uncheck whole word.

Does it take you to a cell/cells?  (do a find all).

Also if you have any, check the data validation lists.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37758982
Just an FYI but worth a read and (IMHO) a YES vote ;)  Its related to this problem and link issues with move/copy: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_9730-Workbook-link-problems-after-copying-tabs-to-a-new-workbook.html
0
 

Author Comment

by:Bright01
ID: 37759191
Dave,

The new WB only has one tab.  So I went to it, clicked "Find", put in the Workbook (source) name; went to options (did not see a "whole word" box) and checked "formulas", "comments" and "values".   Nada.  This is perplexing; even when I check the Range Names in both the WBs, I still don't see a reference that makes sense as a link.

Is there a way for the Macro to "clear all links" prior to the copy and paste?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759260
We can use what I gave you to clear the link

First fun it again and tell me exactly what the link is via that macro I gave you

Also can you type alt e k and select the link ?  Change source to the current workbook?

Are there any charts or DMV lists in the new workbook or not?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759267
Try this also. Delete all range names in new wirkbook save as another name

Does that remove the links?
0
 

Author Comment

by:Bright01
ID: 37759369
Dave,

When I hit alt. E K, I get the "search" screen.

When I delete all of the Range References....... close the WB and reopen..... I do not get any link problems.  It must be in the Range Names.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759453
Ah - HAH.  You can sort the names in the range name manager.  Sort on the REFERS to, then see if you can find the workbook link.

Dave
0
 

Author Comment

by:Bright01
ID: 37759584
Dave,

I've tried everything.  I sorted the range names....... nothing that references the original WB.  I went back and used your Macro.....then tried to find the path..... values, comments, formulas.....nothing.  Could it be that when it copies the WS and posts it, there is created a link?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759594
You're going to have to delete a range name at a time - or block at a time until you find it.

Try this macro:

Sub deleteRangeNameUntilLinksGone()
Dim aLinks As Variant
Dim i As Long
Dim myName As Name
Dim nameToDelete As String

    For Each myName In ThisWorkbook.Names
        nameToDelete = myName.Name
        myName.Delete
        
        If IsEmpty(myWkb.LinkSources(xlExcelLinks)) Then
            MsgBox "Found IT!!!! or at least ONE-> " & nameToDelete
            Exit For
        End If
    Next myName
End Sub

Open in new window


It deletes range names and tests links until the culprit (or the last culprit) is found.  so write down the name, open the workbook again and delete that one, then run the macro again and see if it is the only one.

Dave
0
 

Author Comment

by:Bright01
ID: 37759710
Dave,

I have over 50 Range Names in this model.  Let me give you an update.  I opened the Target WB/WS and checked the Range Names.  Apparently when it copies over, it copies over all of the Range Names (for the entire WB); although I'm simply trying to copy over a single WS in the Source WB.  Here's the interesting part.  If I close the Target WB/WS, and reopen, and select Update Links; then go to the Range Names, I have 5 new Range Names referring back to the original WB.  These 5 Range Names are somehow being updated and added to the Range Name list.

Additionally, I do have a Data Validation set of cells in the WS; but they are in the WS itself; so I would think the copy also copies the DV List.  It does show up in the new WS so I don't think it is linked.

Isn't there an easier way of copying the single WS over the new WB/WS and auto delete ALL Ranges and formulas (i.e. Paste Special VAlues)?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759720
Good question

Do you only need values and formats or also formulas but don't need range names?

If you don't need the range names we could just delete them after the copy

Let me know what bounds to work with and I'll modify accordingly
0
 

Author Comment

by:Bright01
ID: 37759729
Here's how I'm using it.  I have a Workbook that does an extensive amount of analysis on Survey information.  Within the WB, I have the Survey Form.  It has some color formatting and Data Validation (but within the WS itself) but does not need any formulas (no calculations take place on the Survey WB).  It is this Survey Form that is being produced into a separate WB and Tab.  I will need to be able to generate a set of range names but that could be done within the Macro in a specific section (on another Question)....... so for the first part of this, the copy the Survey to another workbook, we could clear all formulas and range names.

Make sense?

Here is the code I'm using to create the Survey WS/WB.


Sub CreateSurvey()
Dim sWBName As String, sWSName As String, bViz As Boolean, bProtect As Boolean
sWBName = "Governance_Survey" 'Add path if you wish (e.g. C:\My folder\NewWorkbook)
sWSName = "Survey"
With Sheets("Survey")
    bViz = .Visible
    .Visible = True
    .Copy
    .Visible = bViz
End With
With ActiveSheet
    bProtect = .ProtectContents
    .Unprotect
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlPasteValues
    .Name = sWSName
    .Range("A1:E226").Select
    Application.CutCopyMode = False
    If bProtect Then .Protect
End With
ActiveWorkbook.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'pick the type of workbook to save
'xlNormal
'xlOpenXMLWorkbook
'xlOpenXMLWorkbookMacroEnabled
ThisWorkbook.Activate
End Sub
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759759
I'm happy to modify the above.  However, let's try this one thing to validate what the finished product would look like.

Put this in your new workbook and run the macro.  It just deletes all the range names.

sub delRangeNames
dim myName as name

for each myName in thisworkbook.names
   myName.Delete
next myName
End sub

After running it, are the links gone?

Dave
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Bright01
ID: 37759769
I must be doing something wrong......

I put the code in the WS, then the Workbook, then a Module....each time I got a Runtime Error (400) each time.........

What am I doing wrong?

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759784
There's nothing wrong with the code, but perhaps the copy/paste of the code didn't work?

You need to advise what line you get the runtime error on.

Put it in a public module.

Sub delRangeNames()
Dim myName As Name

    For Each myName In ThisWorkbook.Names
       myName.Delete
    Next myName
End Sub

Open in new window

Dave
0
 

Author Comment

by:Bright01
ID: 37759794
Dave,

I've got it in Module1.  I get the error at:

myName.Delete

line.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759806
ok - add this, so we know what name its having trouble deleting...

Sub delRangeNames()
Dim myName As Name

    For Each myName In ThisWorkbook.Names
       debug.print "About to delete name: " & myname
       myName.Delete
    Next myName
End Sub

Open in new window


PS - is the sheet protected???

Dave
0
 

Author Comment

by:Bright01
ID: 37759826
Dave,

Sheet is not protected.

I put this code in Module1.

Sub delRangeNames()
Dim myName As Name

    For Each myName In ThisWorkbook.Names
       Debug.Print "About to delete name: " & myName
       myName.Delete
    Next myName
End Sub


I get an error on myName.Delete line.

B.
0
 

Author Comment

by:Bright01
ID: 37759827
I get a Runtime 1004 error......
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759831
What name is displayed in the immediate window?
0
 

Author Comment

by:Bright01
ID: 37759838
Here's the error message.

B.
Error-for-Dave.png
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759844
I get that but what is the name lusted in the immediate window?
0
 

Author Comment

by:Bright01
ID: 37759845
I'm not sure I know what you mean by the immediate window........ but here's the error in the code.

b.
Error-for-Dave2.png
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759847
click on VIEW then Immediate window
0
 

Author Comment

by:Bright01
ID: 37759852
oh...ok....got it.

Here's what it shows;

About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
About to delete name: =#NAME?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759854
Ahh.  now I get it.  At any rate, can all that.  I'm doing a major rewrite.  Sorry this is taking so long.

Dave
0
 

Author Comment

by:Bright01
ID: 37759856
hahaha...... I love this!  Better then Chess!   OK....let me know what I can do to help.

B.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37759869
I just tested this with a dummy survey sheet that had formatting and protection.  Let me know if it works.

Here's the revised code:
Option Explicit

Sub CreateSurvey()
Dim sWBName As String
Dim sWSName As String
Dim bViz As Boolean
Dim bProtect As Boolean
Dim wkb As Workbook
Dim wks As Worksheet
Dim wkbNew As Workbook
Dim wksNew As Worksheet

    sWBName = "Governance_Survey"    'Add path if you wish (e.g. C:\My folder\NewWorkbook)
    sWSName = "Survey"
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Survey")
    
    'retain visible "ness" of the survey sheet
    bViz = wks.Visible
        
    'add new workbook with one sheet only
    Application.SheetsInNewWorkbook = 1
    Set wkbNew = Workbooks.Add
    Application.SheetsInNewWorkbook = 3
    
    Set wksNew = wkbNew.ActiveSheet
    
    'name active sheet of new workbook
    wksNew.Name = sWSName
    
    'copy data from original Survey sheet to new Survey sheet
    wks.Cells.Copy
    wksNew.Cells.PasteSpecial xlPasteAll
    
    'now paste values
    wksNew.Cells.PasteSpecial xlPasteValues
    
    'get protection level of original sheet
    bProtect = wks.ProtectContents
    
    'done with original, so reset visibility
    wks.Visible = bViz
        
    If bProtect Then wksNew.Protect
    
    wkbNew.SaveAs Filename:=Environ("UserProfile") & "\Desktop\" & sWBName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    'pick the type of workbook to save
    'xlNormal
    'xlOpenXMLWorkbook
    'xlOpenXMLWorkbookMacroEnabled
    ThisWorkbook.Activate
End Sub

Open in new window


Because you had invalid names in the workbook, the automated process to delete them could not work.  Not sure whether your original workbook has invalid names or not.  The only way to deal with that is another name manager utility or delete manually, IMO.

See attached for test, then put the code in your workbook (a new copy of it, so we don't lose the other code for the moment) and test.

Crossing fingers.  This copies/pastes to a new sheet and protects, but doesn't copy the sheet, it copies the cells in the sheet, then converts to values as the original macro did, so formats still there DV's still there but no range names, which seem to be the culprit for the moment.

See attached.

Dave
testSurveyCopy-r1.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 37759884
Brilliant!  Copied and executed.... worked!  Haven't completely tested but seems to do what I needed. Next step?  Take results and import back into the Source WB.  You really hung in there on this. ...... again, brilliant.

Dave, very thankful for all of your help.

B.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759894
I guess I should have listened and just rewrote it, but wanted to understand where the problem was confirming, lol.

Dave
0
 

Author Comment

by:Bright01
ID: 37760359
Not many people can troubleshoot Macros via email.  You are one of the few I know.

B.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now