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?
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
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