Solved

Paste Special Error

Posted on 2010-11-16
12
258 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
If you don't know how to downgrade, my instructions below should be helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 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

21 Experts available now in Live!

Get 1:1 Help Now