Solved

Paste Special Error

Posted on 2010-11-16
12
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

756 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