View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Henk Henk is offline
external usenet poster
 
Posts: 80
Default Selection after copy sheet failed

After copying a sheet, I cannot select a cell anymore and I cannot figure out
why. My Sub starts as follows :

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Address = "$N$30" And Range("Year").Value <
Range("LastYear").Value Then

Dim NewYear As VbMsgBoxResult
Dim TabName As String

NewYear = MsgBox("Save a copy?", vbYesNoCancel)

If NewYear= vbCancel Then
Range("Year").Value = Range("LastYear").Value
Range("$N$30").Select
Exit Sub
ElseIf NewYear= vbYes Then
TabName = "Archive " & Range("LastYear").Value
Sheets("Current year").Select
Sheets("Current year").Copy after:=Sheets("Current year")
Sheets("Current year (2)").Name = TabName
Range("A1:AI53").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Rows("54:500").Select
Selection.Delete
End If

The line : Range("A1:AI53").Select

Causes error :

Run-time error '1004':
Select method of Range class failed

Anyone any idea?

Thanks in advance,

Henk