Automation Error help needed

[funkymonkUK]

Hi

I am on a sheet called "Menu". I recorded some code that would copy a
worksheet called "Tables(main)" insert it at the end of the workbook
and then rename it to "TABLES"

which copys the sheets perfectly


Code:
--------------------
Sub viewtables()
'
Sheets("menu").Range("h3").Value = 1
Sheets("Tables(MAIN)").Copy Before:=Sheets(9)
Sheets("Tables(MAIN) (2)").Select
Sheets("Tables(MAIN) (2)").Name = "Tables"
Range("A1").Select
Sheets("menu").Range("h3").Value = ""
End Sub
--------------------


then I have this piece of coding on the the Table(main) sheet code


Code:
--------------------
Private Sub CommandButton1_Click()
If ActiveSheet.Name = "Tables" Then
Application.DisplayAlerts = False
Sheets("menu").Activate
Sheets("Tables").Delete
Application.DisplayAlerts = True
exit sub
End If
Sheets("menu").Activate
End Sub
--------------------


the above is surpose to delete the sheet "Tables" and move back to
"Menu"
It works however i get a error "Run-time error '-2147221080(800401a8)':
Automation error. with only and end button enable so I cannot even click
on debug. however when I click end it has already deleted sheet

 

[Tom Ogilvy]

You would probably need to use application.ontime as the sole command in the
click event to fire a macro that contains the functionality.

Private Sub CommandButton1_Click()
Application.OnTime now,"DeleteSheet"
End Sub

in a general Module

Sub DeleteSheet()
If ActiveSheet.Name = "Tables" Then
Application.DisplayAlerts = False
Sheets("menu").Activate
Sheets("Tables").Delete
Application.DisplayAlerts = True
exit sub
End If
Sheets("menu").Activate
end sub

 

[funkymonkUK]

thnak you that worked perfectly