Selection after copy sheet failed

[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

 

[Jacob Skaria]

Select the desired sheet before you select the range

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


If this post helps click Yes

 

[Sanjay]

Hi there,

Instead of using --> Range("A1:A153").Select
Use --> ActiveSheet.Range("A1:A153").Select
as you are writing the code in the same sheet, so its not needed to define it.
define it when you write the code in ThisWorkbook code window.


Good Luck,
Sanjay


You

 

[Henk]

tHenks Sanjay,

This indeed helped me, but I immediately ran into another problem. The sheet
that is copied contains a lot of VB code, including the Worksheet_Change
macro copying the sheet. So, the moment I am (the macro is) making changes to
the copy of the sheet the same macro on that sheet is started (and errored).
I have made a workaround by an if statement asking whether the sheet the
macro is started from is the original sheet, which works perfectly. But, in
fact I do want to delete all code from the copy of the sheet, directly after
the moment it is created. I do know how to delete a module or form, but how
to delete code from a specific sheet? Do you know?

 

[Henk]

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]

Dear Henk

When you work with WorkSheet Change event always disable the events. as
below. Try and feedback...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

'your code

'your code end here

Application.EnableEvents = True
End Sub


If this post helps click Yes

 

[Henk]

Jacob,

That's a good one!! And so simple! I did not know this existed. Solves a
very lot of other anoying problems. Many tHenks !!!

But still I do want to delete the VB code from the copy of the sheet. Any
brlliant ideas about that?

 

[Jacob Skaria]

Refer the below link on 'Deleting A Module From A Project' by Chip Pearson

http://www.cpearson.com/Excel/vbe.aspx

 

[Henk]

Jacob,

Someone else sent me the same link. Here is my answer :

Joel,

Thanks for your prompt reply. I had been there before and after my visit I
manged to delete Modules and Forms from my VBA projects, but now I want to
delete all code from a specific Worksheet. Chip does not tell how to do that.
Any idea?

Regards,

Henk