asked on

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
    ThisWorkbook.Sheets("Request").Copy
    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
    ActiveWorkbook.Close

   oItem.Subject = strSubject
     
    

    addr1 = "test@gmail.com"
   

    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

ASKER CERTIFIED SOLUTION

Pabilio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbanik

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??
ThisWorkbook.Sheets("Request").Copy

Theva

ASKER

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.
TestFile.xls

Theva

ASKER

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"

SOLUTION

gbanik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pabilio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theva

ASKER

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:

"https://www.experts-exchange.com/questions/26614382/Unprotect-and-Reprotect-VBA-Project.html"

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.

gbanik

Theva, Will get back asap.

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbanik

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.

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theva

ASKER

Hi all,

Thanks for the help and guide.