+ Reply to Thread
Results 1 to 5 of 5

Break internal/local links without manual copy/paste

  1. #1
    Forum Contributor
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    MS Office: Excel 365
    Posts
    163

    Break internal/local links without manual copy/paste

    Hi --

    I often have files with a summary sheet that calculates data from multiple local sheets. Some of the detailed data on those sheets can't be shared and are deleted before sending out the file to a wider audience.

    I'm hoping there's a way to break the links on the summary sheet without having to go the copy/paste values option because I'd still like the local sum formulas to exist and Excel doesn't allow you to copy/paste multiple sections at the same time (& it'd be too tedious to copy/paste each section separately).

    My current move is to transfer the sheet to a blank document, break the links, then move back. Though, I'd rather not have to do this each time if there's a more seamless route.

    Thank you!

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Break internal/local links without manual copy/paste

    What kind/type of links, internal, external ?
    For example:
    1. from formulas (='C:\Temp\[XYZ.xls]Sheet1'!A1, =Sheet2!A1+Sheet3!A1)
    2. from Hyperlinks
    3. from Validation lists
    4. from Conditional formatting
    5. from Named ranges
    etc.
    Present some examples.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Break internal/local links without manual copy/paste

    Perhaps something like:

    Please Login or Register  to view this content.
    Rory

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Break internal/local links without manual copy/paste

    @Rory That's great. Thanks a lot for sharing the code
    I noticed that the code converts the CSE formulas to regular ones. How can CSE formulas be fixed?
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Break internal/local links without manual copy/paste

    You'd have to loop cell by cell for that, I think.

    Edit: actually, a simpler solution may be to only replace formulas that require it:

    Please Login or Register  to view this content.
    Last edited by rorya; 11-27-2019 at 10:59 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-17-2023, 03:50 AM
  2. Warning when sending outside local domain and/or a specific internal email.
    By LHARISIADIS in forum Outlook Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2019, 12:16 PM
  3. Replies: 1
    Last Post: 08-26-2016, 05:06 PM
  4. Break Internal Worksheet links in a file
    By lalaarif1 in forum Excel General
    Replies: 1
    Last Post: 10-11-2015, 03:16 PM
  5. Break internal and external links of selected tabs.
    By lalaarif1 in forum Excel General
    Replies: 1
    Last Post: 05-17-2015, 05:47 AM
  6. Trying to count rows from manual page break to manual page break
    By LuckoftheLefty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2014, 06:53 AM
  7. [SOLVED] Hyperlinks - Move file to local drive, all links break
    By Shawn McGowen in forum Excel General
    Replies: 1
    Last Post: 03-08-2006, 08:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1