Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste link in VBA (2000)

    Hoping someone can suggest some efficient macro code to carry out a fairly simple-sounding task. I have data in Sheet1 and an empty Sheet2. Row by row, I want to copy from Sheet1 and Paste Link into Sheet2, looping through the Sheet1.UsedRange. Certain rows need a custom style applied to their Sheet2 row, but I don't think this would affect the general code for the loop(s).

    Trouble with this is that I'm trying to build the code from recorded macro code, and this involves selection of rows and activation of sheets, and I'm sure there must be a better / more efficient way if this is being done behind the scenes. I just can't seem to find the appropriate methods.

    thanks

    Alan

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Interestingly enough, Paste Links is not part of the Paste Special Method. Maybe Legare can improve on this, but you can try something like:<pre> Sheets("Sheet1").Range("D13").Copy
    Sheets("Sheet2").Range("F36").Activate
    ActiveSheet.Paste Link:=True</pre>

    I take it that you're happy with the row loops.

    HTH
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Yep, happy with looping through the rows. With appropriate sheetnames Set I used something like this in the loop:

    hSheet.Rows(rw).Copy
    oSheet.Rows(oRow).Select
    ActiveSheet.Paste Link:=True

    Looks like I should be using .Activate rather than .Select here. I was actually hoping to find something as basic as an assignment style statement that somehow assigned references rather than values e.g.
    hSheet.Rows(rw) = oSheet.Rows(oRow) with a Paste Link:=True tossed in somewhere to make it all work. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. But perhaps such a technique doesn't exist.

    I was under the impression that using select and activate tended to slow things down... is this true?

    thanks

    Alan

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Forget the previous code <img src=/S/doh.gif border=0 alt=doh width=15 height=15>!. What you're looking for is something like

    TargetRange = "=" & strSourceSheet & "!" & strStartColumn & bytStartRow
    Gre

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    You could do something like this:

    <pre>Dim oCell As Range, oSrc As Worksheet, oTgt As Worksheet
    Set oSrc = Worksheets("Sheet1")
    Set oTgt = Worksheets("Sheet2")
    For Each oCell In oSrc.UsedRange
    oTgt.Range("A1").Offset(oCell.Row - 1, oCell.Column - 1).Formula = _
    "=" & oSrc.Name & "!" & oCell.Address(False, False)
    Next oCell
    </pre>

    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Thanks Legare. I can see that your method avoids the select/ activate methods, and I presume this might be therefore a more efficient (faster) way of doing things. Even though it works cell at a time, rather than row by row?? I've tried the (original) method suggested by Unkamunka, and it basically works except for two aspects:
    1. <LI>A blank cell displays as a zero in the new, referencing cell
      <LI>the used range seems to be double the size of the actual used range (but this could be my coding???)
    Anyway, now I have 2 new toys to play with. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    thanks for your assistance

    Alan

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Edited by Rory to add a closing tag so text wrapped on screen

    My method will also display zeros for blank cells on the original sheet. If you put a formula in a cell that just links to a blank cell, Excel will display zero. There are a number of ways around this. If you know that the blank cells will never be filled with anything, you could not put a link in the target cell when the source is blank like this:

    <pre>Dim oCell As Range, oSrc As Worksheet, oTgt As Worksheet
    Set oSrc = Worksheets("Sheet1")
    Set oTgt = Worksheets("Sheet2")
    For Each oCell In oSrc.UsedRange
    If Ocell.Value = "" Then
    oTgt.Range("A1").Offset(oCell.Row - 1, oCell.Column - 1).Formula = _
    "=" & oSrc.Name & "!" & oCell.Address(False, False)
    End If
    Next oCell
    </pre>

    If you know that the cells may be filled in in the future, then you could change the formula that you insert to display balnk if the linked cell is blank like this:

    <pre>Dim oCell As Range, oSrc As Worksheet, oTgt As Worksheet
    Dim strAddr
    Set oSrc = Worksheets("Sheet1")
    Set oTgt = Worksheets("Sheet2")
    For Each oCell In oSrc.UsedRange
    strAddr = oSrc.Name & "!" & oCell.Address(False, False)
    oTgt.Range("A1").Offset(oCell.Row - 1, oCell.Column - 1).Formula = _
    "=If(" & strAddr & "="""",""""," & strAddr & ")"
    Next oCell
    </pre>


    You can also change the cell formatting to a custom format and not display the zero.

    If the used range is bigger than you think it should be, that usually means that there is some formatting or something else that is making Excel think that it is larger. Also, Excel will not reduce the size of the used range until you save the workbook. So, if you have deleted some rows or columns, you have to save the workbook before the used range gets smaller. Try selecting and deleting the extra rows and columns and saving the workbook to see if the used range will get smaller.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste link in VBA (2000)

    Thank you again, Legare. I had used a similar if statement in ordinary formulae, to substitute a blank for a #VALUE, so its use here is clear. I'm having quite a few problems with this generally, because I'm wanting to deal with "data rows" at the same time as creating cell references... but that's a whole other set of problems. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    BTW, I had to slightly modify your code with the addition of single quote marks (singled out and just visible in <font color=red>red</font color=red>):

    .Formula = "=" & "<font color=red>'</font color=red>" & oSrc.Name & "<font color=red>'</font color=red>" & "!" & oCell.Address(False, False)

    cheers

    Alan

Posting Permissions

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