H
Henk
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
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