Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    PasteSpecial (VBA/Excel/2000)

    The following code was recorded, yet when I try to run the code I am alerted that xlDataValidation is an undeclared variable.

    Can anyone offer some insight?

    <pre>Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 4/13/2005 by W.D. Wells
    '

    '
    Selection.Copy
    Windows("ISS_DUH999_BL.xls").Activate
    Range("K1").Select
    Selection.PasteSpecial Paste:=xlDataValidation, _
    Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End Sub</pre>

    Regards
    Don

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: PasteSpecial (VBA/Excel/2000)

    I'm using 2002, so maybe the versions are different, but in Excel 2002 it looks like you'd want to make 2 changes to the recorded arguments: Change xlDataValidation to xlPasteValidation and change xlNone to xlPasteSpecialOperationNone.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PasteSpecial (VBA/Excel/2000)

    Thanks for the suggestion, but it didn't help.
    Regards
    Don

  4. #4
    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: PasteSpecial (VBA/Excel/2000)

    Don,
    Does it help if you replace xlDataValidation with 6?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: PasteSpecial (VBA/Excel/2000)

    You might want to try applying the PasteSpecial method to the range directly, combining your final two lines of code into this:
    <pre> Range("K1").PasteSpecial Paste:=xlDataValidation, _
    Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False</pre>


  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PasteSpecial (VBA/Excel/2000)

    Thanks Rory
    I believe you've nailed it. I'm at a different location right now, but the behaviour is similar, and replacing the xlDataValidation with 6 and the xlNone with 1 did the trick here. I'll advise you of the effect on the other installation as soon a I get back there and try it out.
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecial (VBA/Excel/2000)

    In fact, you only need to specify the Paste argument; the values you use for Operation, SkipBlanks and Transpose are the default ones, so you might as well omit them:

    Range("K1").PasteSpecial Paste:=xlPasteValidation

    or

    Range("K1").PasteSpecial Paste:=6

Posting Permissions

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