Dear Jacob,

tHenks for your prompt reply. I did not try this one, but tried the solution
Sanjay suggested herunder. Please read my reply on that.

Regards, Henk


"Jacob Skaria" wrote:

Select the desired sheet before you select the range

Sheets("Sheetname").Select
Range("A1:AI53").Select


If this post helps click Yes
---------------
Jacob Skaria


"Henk" wrote:

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