[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Paste Special Error message -RUN TIME ERROR 1004 PASTE SPECIAL METHOD OF RANGE CLASS FAILED

Posted on 2011-03-09
8
Medium Priority
?
668 Views
Last Modified: 2012-05-11
Hi Guys, I have an Excel Macro that works on my Excel 2007 but not my colleagues Excel 2007.
He gets the error message "Run time error 1004 Paste Special Method of Range Class failed
on the line "rtarget Paste Special xlValues" Any ideas why? See below code.


Sub Macro8()
'
Dim i As Integer
Dim rtarget As Range


Dim Daypath As String

Daypath = Worksheets("Macros").Range("Daypath").Value
Daypath = Format(Daypath, "DD.MM.YY")
 
    Workbooks.Open Filename:= _
        "G:\MFL 2011\Cash Products\System Checks & Recs\All Trading Report\12. Mar 11\" & Daypath & " - All Trading.xlsm" _
        , Notify:=False
   
    Sheets("All Trading").Select
    'ActiveSheet.ShowAllData
    Selection.AutoFilter Field:=12, Criteria1:=">5", Operator:=xlOr, _
        Criteria2:="<-5"
    ActiveSheet.Range("$A$1:$X$156000").AutoFilter Field:=4, Criteria1:=Array( _
        "13047", "13049", "13029", "FIRT", "13036", "16004", "HYFRN", "13095", "FIHB", "FINB", "FIMC", "FIRD", "FIRM", "FISC", "TRSY", "FIEMG"), Operator:=xlFilterValues
   
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows("5.Outright MTM Mar Check - Oracle.xlsm").Activate
 
    Set rtarget = Sheets("Trading p&l").Range("A1").End(xlDown).Offset(1)
   
 rtarget.PasteSpecial xlPasteValues
 
0
Comment
Question by:JCutcliffe
[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
  • 3
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35081429
Set rtarget = Sheets("Trading p&l").Range("A1").End(xlDown).Offset(1)
rtarget.PasteSpecial xlPasteValues

You don't have a Select statment to select the place where you want to PasteSpecial your values...maybe that's the problem. Try something like this to see if it works:

Set rtarget = Sheets("Trading p&l").Range("A1").End(xlDown).Offset(1)
rtarget.Select
rtarget.PasteSpecial xlPasteValues

jppinto
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35081554
There should be no need to select. Try replacing this:

Selection.Copy
    Windows("5.Outright MTM Mar Check - Oracle.xlsm").Activate
  
    Set rtarget = Sheets("Trading p&l").Range("A1").End(xlDown).Offset(1)
    
 rtarget.PasteSpecial xlPasteValues

Open in new window


with:
Selection.Copy
Workbooks("5.Outright MTM Mar Check - Oracle.xlsm").Sheets("Trading p&l").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues

Open in new window

0
 

Author Comment

by:JCutcliffe
ID: 35081675
Why does it work on my 2007 Excel computer but not my Colleagues?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35081718
Don't know specifically - perhaps he has some other code running that clears the clipboard when your code switches workbooks.
0
 

Author Comment

by:JCutcliffe
ID: 35082493
How do I investigate that? Bizarely, his computer also deletes Named Ranges in Excel whenever
he opens a specific spreadsheet. Any idea why?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35083812
Try opening Excel in Safe Mode (hold down Ctrl while opening normally) then see if the same behaviour occurs.
0
 

Author Comment

by:JCutcliffe
ID: 35096624
For some reason too, Named Ranges keep disappearing in my colleagues Excel 2007 spreadsheet
when he runs the same spreadsheet. Any idea why this would happen?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35096767
Does he have his copy of Office fully patched up to date? I have heard of disappearing named ranges before but I thought that had been fixed.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

656 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