Excel Macro works only one time after opening the file. To perform similar operation I have to close and reopen.

Question

I have written a code that makes selection and copy paste certain cells based on drop down. code on the main file is

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("$Q$28")
Call Get4
End Sub

The dropdown has 6 options. Every time I open the file and select any of the 6 dropdown options it performs the desired operation. The second time when I change the option it doesn't perform any operation . Again after closing and reopening the same sheet and selecting any option of the 6 option it works. so the Macro works only one time and not after that. Please let me know how to make it work multiple times. here is my code please take a look.

Sub Get4()
Dim ws As Worksheet

Application.EnableEvents = False

ActiveWorkbook.Sheets("4V").Range("A01:AN70").Clear
For Each Shp In ActiveWorkbook.Sheets("4V").Shapes
If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl) Then Shp.Delete 
Next Shp

'For Each ws In ActiveWorkbook.Sheets
'ws.Visible = xlSheetVisible

'Next

If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical , Reduced Mesh Pad , Half pipe" Then
Sheets("4-10").Select
Range("A01:AN67").Select
Selection.Copy

 Sheets("4V").Select
Range("A01:AN67").Select
 ActiveSheet.Paste

Else

 If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical , Reduced Mesh Pad , Baffle" Then
Sheets("4-11").Select
Range("A01:AN67").Select
Selection.Copy

Sheets("4V").Select
Range("A01:AN67").Select
ActiveSheet.Paste

Else

If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical , Mesh Pad , Half pipe" Then
Sheets("4-12").Select
Range("A01:AN67").Select
Selection.Copy

Sheets("4V").Select
 Range("A01:AN67").Select
ActiveSheet.Paste

Else

If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical ,Mesh Pad ,Baffle"    Then
 Sheets("4-13").Select
Range("A01:AN67").Select
Selection.Copy

Sheets("4V").Select
Range("A01:AN67").Select
ActiveSheet.Paste

Else

 If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical , No Mesh Pad , Half pipe" Then
Sheets("4-15").Select
Range("A01:AN67").Select
Selection.Copy

Sheets("4V").Select
Range("A01:AN67").Select
 ActiveSheet.Paste

Else

If ActiveWorkbook.Sheets("Main").Range("Q28") = "Vertical , No Mesh Pad , Baffle" Then
Sheets("4-14").Select
Range("A01:AN67").Select
Selection.Copy

Sheets("4V").Select
Range("A01:AN67").Select
ActiveSheet.Paste

End If
End If
End If
End If
End If
End If

End Sub

Answer 1

Set the enable events back to true. When it's false, events in the workbook won't fire. Place: Application.EnableEvents = True at the end of your sub.