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

[ajdinspector]

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:

'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:

'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!

 

[Leith Ross]

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.

'copy and paste the data
CopyData.Copy
addme.Select
addme.Parent.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False, NoHTMLFormatting:=True

 

[ajdinspector]

Hi Leith! thanks so much for the response! i entered that bit, now i have

'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"

 

[ajdinspector]

Can anyone help me out with this error?