Paste special method failed...

markc · Mar 15th 2005

Hi all -

I keep receiving
"Paste Special method of range class failed"

I've tried re-writing the code several different ways,
none of which works.

Can you give me a pointer at what I'm doing wrong?

Thanks
-marc

Code

Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10").PasteSpecial _
     Paste:=xlValues, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False

Timbo · Mar 15th 2005

Re: Paste special method failed...

Try this -

Code

Workbooks("AR_ANALYZE").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False

markc · Mar 15th 2005

Re: Paste special method failed...

Timbo -

Tried your code
Came back Subscript out of range
Highlighted here

Code

Workbooks("AR_ANALYZE").Activate

I tried commenting the line out
Came back with paste special method failed

Also, most of what I've read on the forum suggests
not using Select

Indeed I ran into some problems with slow code execution
due to select command.

See my previous post Re: slow code execution.
Thanks
-marc

**norie** · Mar 15th 2005

Re: Paste special method failed...

marc

How are you running your code?

By the way try AR_ANALYZE.xls in Timbo's code.

markc · Mar 15th 2005

Re: Paste special method failed...

Hi Norie -

Here is the corrected code :

Code

Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Still receive the "Paste special..." error

To run
Alt + F8 to activate macro menu
Click macro
Run

The controlling macro actually calls several subprocedures
of which the above is a part.

Complete:

Code

Sub AR_Analyze()
    Application.ScreenUpdating = False
    Call Clear_Old
    Call OpenSelectedFile
    Call GetData
    Application.ScreenUpdating = True
End Sub


Sub Clear_Old()
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10:F65536").Clear
End Sub


Sub GetData()


    Application.DisplayAlerts = False
    


Dim lngRows As Long


    lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

OpenSelectedFile procedure is in a separate module.

Thanks
-marc

slickity · Oct 31st 2005

Re: Paste special method failed...

I have the same error message with some code of mine. The line that gets highlighed is

Code

Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Here's the complete code:

Code

Sub detachReview()


    Dim originalWB As String
    Dim newWB As String
    Dim newWBName As String
    
    originalWB = ActiveWorkbook.Name
    
    newWBName = "Costing Review - OI " & Range("'Start'!C5").Value


    Application.ScreenUpdating = False
    
    Workbooks.Add
    newWB = ActiveWorkbook.Name
    
    Application.WindowState = xlMinimized
    
    Windows(originalWB).Activate


    Sheets("Project Review").Activate
    Call formatReview
    
    Range("A1:J117").Select
    Selection.Copy
    Windows(newWB).Activate
    Sheets("Sheet1").Activate
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
        , Transpose:=False
    Range("A1").Activate
    Selection.PasteSpecial Paste:=xlColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Name = "Costing Review"
        
    Sheets("Sheet2").Select
    Selection.Delete
    Sheets("Sheet3").Select
    Selection.Delete
    
    Windows(originalWB).Activate
    
    Application.CutCopyMode = False
    Call deFormatReview
    Range("A1").Select
    
    Windows(newWB).Activate
    
    Application.ScreenUpdating = True
End Sub

Display More

Paste special method failed...

Participate now!

Share

Users Viewing This Thread