Consulting

Results 1 to 6 of 6

Thread: VBA PasteSpecial error

  1. #1
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location

    VBA PasteSpecial error

    Hi Team,


    I need your help in understanding paste special , why it throws error when we write in a single line.


    This throws copile error at xlPasteValues
    Sheet1.Range("A1").CurrentRegion.Copy Sheet2.Range("A1").PasteSpecial xlPasteValues

    This works
    Sheet1.Range("A1").CurrentRegion.Copy
    Sheet2.Range("A1").PasteSpecial xlPasteValues



    Regards,
    mg
    Last edited by Bob Phillips; 08-15-2019 at 04:47 AM. Reason: Legibility

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I think the easy answer is "Because that's the way it is"


    I'm guessing here about how the internals of Excel work, but I think that

    1. .Copy puts the input Range on the Clipboard, and then Pastes (plain ol' Paste) it to the Output Range if the second parameter is supplied

    2. .PasteSpecial assumes that there is something on the Clipboard already and processes it to make it "Special" before putting the results in the Output Range


    Did I mention that this is just a guess?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    My understanding is that
    .Copy Destination:=
    bypasses the clipboard therefore there is nothing to paste.
    So you have to do it as two separate opertions

  4. #4
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Another and faster solution is to copy the range without using the clipboard:
        Dim rngS As Range    
        Set rngS = Sheet1.Range("A1").CurrentRegion
        
        With rngS
            Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
    This method requires the size of the target range, not just the upper left corner of the range as with PasteSpecial.
    But it also has its advantages.
    Eg CurrentRegion is the range A1: Z20. In the target range, we only need the first two columns
        Dim rngS As Range
        
        Set rngS = Sheet1.Range("A1").CurrentRegion
        
        With rngS
            Sheet2.Range("A1").Resize(.Rows.Count, 2).Value = .Value
        End With
    Artik

  5. #5
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Hi Paul,Fluff and Artik,

    Thank you all for the information and clearing my doubt.

    Regards,
    mg.

  6. #6
    You're welcome & thanks for the feedback

Posting Permissions

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