Paste Special Error

Hi Experts,

I would like to request Experts help. The attached script shows an error as “Paste Special method of range class failed” at line 17:

ActiveSheet.Range("AB:AC").PasteSpecial xlPasteValues

Hope Experts could help me to fix this error.

Function SendMsg(strSubject As String, _
                   strBody As String, _
                   strTO As String, _
                   Optional strDoc As String, _
                   Optional strCC As String, _
                   Optional strBCC As String)
    Dim oLapp
    Dim oItem
    Dim myattachments
    Dim fs As String
    Set oLapp = CreateObject("Outlook.Application")
    Set oItem = oLapp.CreateItem(olMailItem)
    Application.DisplayAlerts = False
    ActiveSheet.Range("AB:AC").PasteSpecial xlPasteValues
    Call deleteButton2
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & Sheets("Request").Cells(1, "i") & "_" & Sheets("Request").Cells(5, "I") & "@" & Format(Sheets("Request").Range("IR12").Value, "hh'mm'ss") & ".xls", FileFormat:=-4143
    Application.DisplayAlerts = True
    fs = ActiveWorkbook.FullName

   oItem.Subject = strSubject

    addr1 = ""

    oItem.To = addr1 + ";" + addr2 '+ ";" + addr3

    oItem.CC = strCC
    oItem.BCC = strBCC
    'oItem.BodyFormat = olFormatHTML
    oItem.htmlbody = strBody
    oItem.Importance = olImportanceHigh
    oItem.attachments.Add fs
   oItem.display 'send
   Kill fs
    Set oLapp = Nothing
    Set oItem = Nothing
End Function

Open in new window

Who is Participating?
PabilioConnect With a Mentor Commented:
Hi Theva,

I think the error is due that you are copiying an entire sheet (Request) and then the code is triying to paste it in a limited range (AB:AC)...
You should copy and paste range of similar size (AB:AC = 2 columns)... so you should copy only the two columns from "Request" that you need to paste the values in AB:AC.
Where you have: ThisWorkbook.Sheets("Request").Copy
There should be: ThisWorkbook.Sheets("Request").Range ("Col 1 : Col 2").Copy
Being Col1 and Col2 the columns you want to copy from Request Sheet.

Hope it helps,
Though I have not yet figured out what u r doing ... but at the outset...
U copying a complete sheet and trying to paste it over on 2 columns.
What are u trying to do?

Were you trying something else at this line??
ThevaAuthor Commented:
Hi Gbanik,

Here's the sample workbook that I've generated from the attached script. If you noticed there are error "Name?" at column AB:AC just because the original workbook has formula to create GMT time. If the specific range were copied as Paste Special that could eliminate this error. I hope so.  

P/S: Actually the code for GMT has already copied at Request sheet, but not sure why it was not displayed properly.  
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

ThevaAuthor Commented:
Hi Pabilio,

Have tested.It shows error as "Application-Defined or Object defined error" at this line:
 "ThisWorkbook.Sheets("Request").Range ("Col 1 : Col 2").Copy"
gbanikConnect With a Mentor Commented:
Here is your working file. When u add a custom function, add it in a new module. Cheers!
PabilioConnect With a Mentor Commented:
Hi Theva,

In  "ThisWorkbook.Sheets("Request").Range ("Col 1 : Col 2").Copy"
You should Change Col 1 and Col 2 for the Column Letters that You need to copy from Request Sheet
i.e. ThisWorkbook.Sheets("Request").Range ("F:G").Copy  (If Columns F and G are the two columns that you are triying to paste values in Columns AB:AC

ThevaAuthor Commented:
Hi gbanik,

Originally the code was created as module but I have to problem to run the code when I locked the VBA project. Hope you will take a look at this threat:


Therefore, I have decided to move the module in sheet.

If we able to copy the range (AB:AC) from the source, I believe we could resolve the whole problem.
Actually I'm struggling with this problem almost  a week.  
Theva, Will get back asap.
NorieConnect With a Mentor VBA ExpertCommented:
You could try using one cell as the destination for the paste rather than trying to specify the exact range.

I don't mean any cell, try using the top left cell of the range you want to copy to, not sure what that might be in your example but perhaps AB1.

By the way why have you moved the code?

I know the code is copying data to a specific worksheet but that doesn't mean the code should go in the worksheet's module.

The only code that should go in a worksheet module is normally event code eg things like Worksheet_Change...
Theva... I went through your other question. But, in order to run custom functions, you will have to put them as globally available functions and hence in an independent module. They cant be run from a sheet. I dont know the answer for your other question... but as for this one, this is the only way.
NorieConnect With a Mentor VBA ExpertCommented:

I've read the other thread and I've got to agree with some of what's said - you shouldn't use code to do this sort of thing.

It's just going to cause problems, and if the project(s) are protected then that just makes things worse.

As for the code not working when the project is protected, then that shouldn't happen.

If it does it something wrong with the code itself not because it's protected.
ThevaAuthor Commented:
Hi all,

Thanks for the help and guide.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.