Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PSpecial problem (XL2000 SR1)

    I have the following 2 macros assigned to buttons:

    Sub PasteSpecialFormulas()
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub

    Sub PasteSpNoBorders()

    Selection.PasteSpecial Paste:=xlAllExceptBorders, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub

    The first one works fine, the second one doesn't give an error message but it leaves the destination cell blank. Can anyone help please?

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

    Re: PSpecial problem (XL2000 SR1)

    Same thing on my XL2K, and the only reason I can see is a bug in XL. If I use Paste Special from the menu and select "All Except Borders" it works fine, but I can not get it to work from a macro. I also searched Knowledgebase, but did not get any hits there.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PSpecial problem (XL2000 SR1)

    A bug indeed. If you look in the object browser, you'll see xlPasteAllExceptBorders has a value of 7.

    If you replace xlPasteAllExceptBorders with 7 in the code it works.

    I used this code to test:

    Sub test()
    Dim i As Integer
    For i = 1 To 10
    On Error Resume Next
    Range("C3").Offset(i).PasteSpecial Paste:=i, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    If Err = 0 Then
    End If
    Next
    End Sub

    The order matches the order in the paste special dialog. (top-down, left-right)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PSpecial problem (XL2000 SR1)

    Brilliant - thanks Jan.

    Thanks also Legare, I feel less inadequate when you don't see a solution to my problem.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PSpecial problem (XL2000 SR1)

    Mike,
    Q171281 has a full explanation. I guess they didn't see the need to fix it in XL2k! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I seem to recall coming across at least one other of these errors with built-in constants working from a recorded macro but not if you write the code yourself.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: PSpecial problem (XL2000 SR1)

    This is indeed a strange bug. If I go to the VBE Immediate window and enter:

    <pre>?xlPasteAllExceptBorders
    </pre>


    It displays 7.

    However, if I add:

    <pre>Const xlAllExceptBorders = 7
    </pre>


    to the beginning of the Sub, then it works.
    Legare Coleman

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

    Re: PSpecial problem (XL2000 SR1)

    Jan

    In my Object Browser PasteSpecialOperations lists only add/subtract/add/divide/none.


    Rory

    Thanks for the KB article, it has some great Microsoft doublespeak:

    "To paste information except borders, the xlPasteAllExceptBorders constant should use a value of 7. "

    "There is no constant that causes all information except borders to be pasted. "

    "Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000. "

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: PSpecial problem (XL2000 SR1)

    There are more bugs in the article than in the code!
    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PSpecial problem (XL2000 SR1)

    Hi Michael,

    <<In my Object Browser PasteSpecialOperations lists only add/subtract/add/divide/none>>

    I know, in mine too. Then I just ran the macro I posted earlier and discovered they simply haven't assigned constants for the other operations (and assigned one wrong in the process somehow). This makes one wonder what other operations (constants) are hidden from us, ready to be discovered <bg>.

    Apparently the bug of xlpasteallexceptborders being 6 in XL97 was fixed (in XL2000 it shows as 7 in the object browser), but it still behaves as if it's 6 when used in code?!?!?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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