Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Paste Special Error

Posted on 2010-11-16
12
Medium Priority
?
265 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 800 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
Independent Software Vendors: 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!

 

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 400 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 800 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 34

Assisted Solution

by:Norie
Norie earned 800 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 34

Assisted Solution

by:Norie
Norie earned 800 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

704 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