PasteSpecial does not work in VBA code

rameshjeni · Apr 12th 2012

I have attached the code where I am trying to select a user range thru a subroutine and use this generically to copy from one sheet and paste it to another sheet I am using pastespecial as I want to copy only the format and not the values. The code dos not work and gets stuck at highlighted line in the attachment.the message i get is Run time error 1004 . pasteSpecial method of range class failed Any suggestions to correct this error?

Code

Option Explicit
Public UserRange As Range
 
Sub SelUserRange()
'Selects Range based on user inpout
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Delete Blank Rows ", Type:=8)
Application.Goto UserRange
'Msgbox("UserRange is" & UserRange, vbOKOnly, "JRM", 8) As VbMsgBoxResult
End Sub
 
Sub RangeCopyPaste()
    Sheets("Sheet1").Select
    SelUserRange
    Application.CutCopyMode = False
    Selection.Copy
   
    Sheets("COGNI").Activate
    SelUserRange
    UserRange.Activate
    UserRange.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Exit Sub
 
Canceled:
   
End Sub

Display More

The crux of the problem

First sub selects a range to be copied in Sjheet1 as specified by the user
Second subroutine activates a sheet “COGNI”: and tried to paste into a location specified by user thru input box
The macro is stuck at highlighted point

**rory** · Apr 12th 2012

Re: Pastespecial does not work in VBA code

I would suggest you do something like this:

Code

Option Explicit 
Function SelUserRange() As Range
     'Selects Range based on user inpout
    Set SelUserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Delete Blank Rows ", Type:=8)
End Function
 
Sub RangeCopyPaste()
    Dim CopyRange As Range
    Dim PasteRange As Range
    Sheets("Sheet1").Select
    Set CopyRange = SelUserRange
    Application.CutCopyMode = False
     
    Sheets("COGNI").Activate
    Set PasteRange = SelUserRange
    CopyRange.Copy
    UserRange.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Exit Sub
     
Canceled:
     
End Sub

Display More

rameshjeni · Apr 13th 2012

Re: Pastespecial does not work in VBA code

Thanks for the quick response. While running it I get the error "Compile error :object required". Am I missing something?

**rory** · Apr 13th 2012

Re: Pastespecial does not work in VBA code

Sorry, I forgot to change a variable:

Code

UserRange.PasteSpecial xlPasteFormats

should be

Code

PasteRange.PasteSpecial xlPasteFormats

rameshjeni · Apr 14th 2012

Re: Pastespecial does not work in VBA code

Thank Rory, it works now.

PasteSpecial does not work in VBA code

Participate now!

Share