Paste method of worksheet class failed error

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

This is a code stored in my Personal.Xls:

wb.Worksheets(1).Range("C5").Select
ActiveSheet.Paste

When I run the below code, I get the debug error "Paste
method of worksheet class failed" on the above part of the
code. What should I do to get it to not give me this
error?

Dim wb As Workbook
If MsgBox("Would you like to Import Data?", vbYesNo) =
vbNo Then
Exit Sub
End If
On Error Resume Next
Set wb = Workbooks("Stats Manager.xls")
On Error GoTo 0
If wb Is Nothing Then Set wb = Workbooks.Open("P:\Stats
Manager.xls")

With Worksheets(1)
Set rng = .Range("A4:Z100")
Set rng1 = .Range("A4:AZ4")
End With
res = Application.Match("Ext", rng1, 0)
If Not IsError(res) Then
Set rng2 = rng1(1, res)
rng.Sort Key1:=rng2, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End If

wb.Worksheets(1).Range("C5").Select
ActiveSheet.Paste

Range("B5:AZ100").Select
Range("AZ5").Activate
With Selection
.HorizontalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A2:AZ3").Select

MsgBox "Data Imported Successfully!", vbOKOnly

If MsgBox("Would you like to Save Workbook?", vbYesNo) =
vbNo Then
Exit Sub
End If
ActiveWorkbook.Save
 
1. Your Paste code line is not as explicit as that required. We should
evidently, use something like :-
'-----------------------------------------------------------------------------
ActiveSheet.Paste Destination:=Worksheets(1).Range("C5")
'-----------------------------------------------------------------------------

Seems like a "belt and braces" approach - but it is probably "
feature, not a bug". Despite this, I do get inexplicable problems wit
Paste at times. Generally find that the following produces bette
results than using Select or Activate:-

'----------------------------------------------------------------------------
Application.Goto reference:=wb.Worksheets(1).Range("C5")
'----------------------------------------------------------------------------
 
Back
Top