Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Pasting "Special"-Links

    I am pasting links from large spreadsheets, with many of the cells on the source (i. e., linked) spreadsheet having no contents. When I past "Special", and click on "Paste Link", those source cells appear on the destination spreadsheet as zero values, in the same format as the source worksheet. As a result, I am then forced to manually clear these values/contents from the destination spreadsheet, and this is very laborious. Any tips? Thanks in advance.[img]/w3timages/icons/crazy.gif[/img]

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting "Special"-Links

    [img]/w3timages/icons/doh.gif[/img]I'm not sure I understand what the problem is. What's happening is what Paste Link does. Can you give us more information about why it's a problem?

    Jon

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting "Special"-Links

    If you're clearing the contents, you're basically clearing the link - I don't know that you really want to do that. One suggestion would be to use conditional formatting to make cells with a value of zero have a white font so it doesn't show on your spreadsheet. However, if a cell is supposed to have a value of zero, then that cell won't show, either. HTH.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pasting

    Sorry I wasn't clearer. Example: I have two spreadsheets-one is the "source" that has data (including links to still another spreadsheet) entries on every other row in 8 columns. The other rows on the source spreadsheet have no contents (they are blank). The second spreadsheet is the spreadsheet to which I want to paste the data/links that are on the source spreadsheet. When I attempt to do so, the results on the spreadsheet to which I pasted the data/links from the source spreadsheet include zero entries in the rows of the spreadsheet to which I have pasted that correspond to the blank rows (i. e., rows with no data/links) of the source spreadsheet. I then have to laboriously "clear the contents" of all of these blank rows on the spreadsheet to which I have pasted the data/links from the source spreadsheet so that it will have the same appearance as the source spreadsheet. I hope this will help. Thanks.

  5. #5
    calacuccia
    Guest

    Re: Pasting

    There is no direct solution for this, but you could use a macro, which delete all the links to empty cells afterwards.

    Instead of using PasteSpecial/Link, use this macro:

    Sub PasteLink_blanksExcluded()

    ActiveSheet.Paste Link:=True

    For Each r In Selection
    If Workbooks(Mid(r.Formula, 3, InStr(1, r.Formula, "]") - 3)).Worksheets(Mid(r.Formula, InStr(1, r.Formula, "]") + 1, InStr(1, r.Formula, "!") - InStr(1, r.Formula, "]") - 1)).Range(Right(r.Formula, Len(r.Formula) - InStr(1, r.Formula, "!"))) = "" Then r.Formula = ""
    Next r
    End Sub

    Calacuccia

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pasting "Special"-Links

    One workaround is to make all the blank cells on the source spreadsheet contain a space. Then they will link correctly.

    However this may have consequences for formulae which reference the "blank" cells.
    David Grugeon
    Brisbane Australia

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting

    One question: Can you get rid of the blank lines in the original?

    Jon

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pasting

    I have never worked with macros before-where do I insert it?

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pasting

    No-need to include them.

  10. #10
    calacuccia
    Guest

    Re: Pasting

    You can include them in different ways, in a workbook you work with , in an Excel add-in, as many developpers do, or in a personal workbook.

    The last way is probably the best, so:

    1/ In Excel, locate your XLStart directory, normally it is here:
    C:Program FilesMicrosoft OfficeOfficeXLStart

    2/ Look if you have a personal.xls with Excel open. You can see this if you do this in Excel:
    Window/Unhide, if you see a personal.xls, just click OK to unhide it, and go forward to step 4. If you don't have it see step 3 to create it.

    Personal.xls or any workbook in the XLStart directory is always opened whenever Excel is opened. If you make such workbook hidden, as personal.xls is by default you even never see that it is there, so many users don't even know of this workbook. Its purpose is exactly what you're looking for here, store personalised macros.

    3/ Create personal.xls
    Just open a blank workbook, and save it as C:Program FilesMicrosoft OfficeOfficeXLStartpersonal.xls

    4/ Put the macro in personal.xls
    With personal.xls opened, hit Alt+F8, and type the macro name in the box 'Macro Name', e.g. SpecialPasteLinks, and then hit the create button.

    5/ Now you will see the Visual Basic Editor opening and showing this empty procedure:

    Sub SpecialPasteLinks()
    End Sub

    All you have to do is to paste the code I wrote aboe between both lines, Sub SpecialLinks() is the start of the procedure, and it will be ended by End Sub, so you need to put the code between it:

    Sub SpecialPasteLinks()
    ActiveSheet.Paste Link:=True

    For Each r In Selection
    If Workbooks(Mid(r.Formula, 3, InStr(1, r.Formula, "]") - 3)).Worksheets(Mid(r.Formula, InStr(1, r.Formula, "]") + 1, InStr(1, r.Formula, "!") - InStr(1, r.Formula, "]") - 1)).Range(Right(r.Formula, Len(r.Formula) - InStr(1, r.Formula, "!"))) = "" Then r.Formula = ""
    Next r
    End Sub

    Now close the VBA Editor.

    6/ Create a menu item in the Paste menu bar:
    Right-click the main Excel menu, select Customize.
    Choose Tab 'Commands' of the Customize Dialog
    In Categories, scroll down to macros, now drag and drop the 'Custom Button' icon on the menu bar somewhere inside the Paste icons.
    Then right-click the created icon, and choose 'Assign Macro', from the dialog choose 'SpecialPasteLinks'. Finally, right-click the icon again, and select 'Change Button Image' to chang the icon to your desire.

    7/ Finally, save personla.xls, and thne hide it, using Window/Hide. Now close Excel, and answer yes if prompted to save changes in Personal.xls.

    Now you have created a custom Paste function with a custom icon.

    Calacuccia

  11. #11
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting "Special"-Links

    Why use 'Paste Links'? Think the other way around and make straight links on your destination sheet to the source sheets with a simple formula like:
    =IF(SourceCell="","",SourceCell) where you just click on the cell you want in the source worksheet to fill in the SourceCell entry. I avoid the Paste Links operation when working in Excel because it used to create 'arrays' when working with multi-cell ranges which were sometimes difficult to deal with when modifing the destination sheet.

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pasting

    Are you already aware of the option to simply supress the display of the zero values (Tools - Options - Views -remove checkmark from ZERO VALUES)?

Posting Permissions

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