Automation Error help needed

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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

--
Regards,
Tom Ogilvy


"funkymonkUK" wrote:

>
> 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
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=544665
>
>

thnak you that worked perfectly