xlPasteValues not working, need to match destination format, but getting error
Results 1 to 4 of 4

Thread: xlPasteValues not working, need to match destination format, but getting error

  1. #1
    New Member
    Join Date
    Nov 2018
    Location
    Denver, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default xlPasteValues not working, need to match destination format, but getting error

    Hi! So i have a data set that is exported by a database, so can't change it's format. I have a script to copy and paste it into my sheet and it works fine:
    Code:
    'set the range reference variables
    Set Bk = Sheet3.Range("C5") 'file path of book to import from
    Set Sh = Sheet3.Range("F5") 'sheet to import
    Set St = Sheet3.Range("G5") 'starting cell reference
    Set Fn = Sheet3.Range("H5") 'finishing cell reference
    Set Tb = Sheet3.Range("I5") 'sheet in this workbook to send it to
    'set the destination
    Set addme = Worksheets(Tb.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    'open the workbook
    Set wb = Workbooks.Open(Bk)
    'set the copy range
    Set CopyData = Worksheets(Sh.Value).Range(St & ":" & Fn)
    'copy and paste the data
    CopyData.Copy
    addme.PasteSpecial xlPasteValues
    One of my formulas doesn't recognize the data as a date, so i want to "Match Destination Formatting (M)" just like a right-click special paste. so this is what i tried:
    Code:
    'copy and paste the data
    CopyData.Copy
    addme.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
            False, NoHTMLFormatting:=True
    and i get the error "Compile error: Named argument not found"
    What am i doing wrong here? Thanks in advance! Also, this is only my second post so if i need to show more or less of my code, or something else to improve future postings please let me know. Thanks!
    Last edited by ajdinspector; May 31st, 2019 at 12:21 PM.

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,773
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlPasteValues not working, need to match destination format, but getting error

    Hello

    "addme" is a Range and your code is doing a "PasteSpecial" to the Worksheet from the clipboard. You need to first select the cell where the data is to be pasted from the clipboard and then perform the operation on the worksheet itself. The Parent property of a Range object returns the Worksheet to which it belongs.

    Code:
    'copy and paste the data
    CopyData.Copy
    addme.Select
    addme.Parent.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
            False, NoHTMLFormatting:=True
    Last edited by Leith Ross; May 31st, 2019 at 02:30 PM.
    Sincerely,
    Leith Ross

  3. #3
    New Member
    Join Date
    Nov 2018
    Location
    Denver, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlPasteValues not working, need to match destination format, but getting error

    Hi Leith! thanks so much for the response! i entered that bit, now i have
    Code:
    'copy and paste the data
    CopyData.Copy
    addme.Select
    addme.Parent.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
            False, NoHTMLFormatting:=True
    but i'm getting :
    "An Error has Occurred
    The error number is: 1004
    Select method of Range class failed
    Please notify the administrator"
    Last edited by ajdinspector; May 31st, 2019 at 02:54 PM.

  4. #4
    New Member
    Join Date
    Nov 2018
    Location
    Denver, Colorado
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlPasteValues not working, need to match destination format, but getting error

    Can anyone help me out with this error?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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