• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

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

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
JCutcliffe
Asked:
JCutcliffe
  • 4
  • 3
1 Solution
 
jppintoCommented:
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
 
Rory ArchibaldCommented:
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
 
JCutcliffeAuthor Commented:
Why does it work on my 2007 Excel computer but not my Colleagues?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rory ArchibaldCommented:
Don't know specifically - perhaps he has some other code running that clears the clipboard when your code switches workbooks.
0
 
JCutcliffeAuthor Commented:
How do I investigate that? Bizarely, his computer also deletes Named Ranges in Excel whenever
he opens a specific spreadsheet. Any idea why?
0
 
Rory ArchibaldCommented:
Try opening Excel in Safe Mode (hold down Ctrl while opening normally) then see if the same behaviour occurs.
0
 
JCutcliffeAuthor Commented:
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
 
Rory ArchibaldCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now