Solved

Paste Special Error

Posted on 2010-11-16
12
259 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Theva
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 5

Accepted Solution

by:
Pabilio earned 200 total points
ID: 34144098
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,
Roberto.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34144107
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
0
 

Author Comment

by:Theva
ID: 34144730
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
0
 

Author Comment

by:Theva
ID: 34144905
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"
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 100 total points
ID: 34144945
Here is your working file. When u add a custom function, add it in a new module. Cheers!
TestFile.xls
0
 
LVL 5

Assisted Solution

by:Pabilio
Pabilio earned 200 total points
ID: 34145072
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

Roberto.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Theva
ID: 34145136
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:

 "http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26614382.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.  
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34145554
Theva, Will get back asap.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 200 total points
ID: 34156610
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...
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34157820
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.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 200 total points
ID: 34158708
Theya

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.
0
 

Author Closing Comment

by:Theva
ID: 34190352
Hi all,

Thanks for the help and guide.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't know how to downgrade, my instructions below should be helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now