Some more info-
I currently have an Excel sheet with 20 different P&Ls. I have written a macro to copy each seperate PL out for email to budget holders.
When I copy, the new sheet retains the links. These are the links I wish to break and range value.
I originally recorded the code, but this (obviously) hard codes the "linked to" file.
I thought that with some combination of the code I could make it neater and more user friendly
Thanks
Excel - question
I have a file that a macro copies a few sheets out of and breaks link. I want to write some code that will break the link rather than hard code.
So far I have
x = ActiveWorkbook.Path
y = ActiveWorkbook.Name
z = x + y
ActiveWorkbook.BreakLink Name:= _
x, Type:=xlExcelLinks
However I can not get it to work, I have also tried X in "".
.
Replies (3)
Please login or register to join the discussion.
Run this code in the file in which you want the links removed
You may already have found an answer, I haven't been in AA for a while.
In the workbook in whcih you want he links broken run ths follwoing code. This loops though all the links that exist (from the LinkSources bit) and then deletes them (the break link bit).
Sub BreakLinks()
Dim xLinks As Variant
xLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
Dim i
If Not IsEmpty(xLinks) Then
For i = 1 To UBound(xLinks)
ActiveWorkbook.BreakLink Name:=xLinks(i), Type:=xlLinkTypeExcelLinks
Next i
End If
End Sub
Use PasteSpecial
So you have copied the entire sheet with something like:
Activesheet.Copy
If so, then you just need to target the UsedRange on this sheet and copy and pastespecial values:
Activesheet.Copy
'the new activesheet is the copy:
Activesheet.UsedRange.Copy
Activesheet.Range("A1").PasteSpecial xlPasteValues
Richard
Please describe what you want to achieve
Hi
it isn't clear to me what it is you are wanting to achieve - please could you describe in detail what you currently have, what you would like, and how you believe this can be achieved. You mention breaking links - is this because you have some formulas targeting external references that you now wish were just values, or something else?
Thanks
Richard