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

ajdinspector

New Member
Joined
Nov 5, 2018
Messages
8
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:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
2010, 2007
Platform
Windows
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:

ajdinspector

New Member
Joined
Nov 5, 2018
Messages
8
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,650
Messages
5,469,943
Members
406,674
Latest member
MrSTruct

This Week's Hot Topics

Top