UPDATED Break links VBA

UPDATED Break links VBA

Didn't find your answer?

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.

avatar
By rpbunce
10th Jul 2008 13:54

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

Thanks (0)
avatar
By RichardSchollar
17th Jun 2008 09:30

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

Thanks (0)
avatar
By RichardSchollar
16th Jun 2008 21:19

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

Thanks (0)