Delete Worksheet If It Exists

Re: Delete Worksheet If It Exists

click debug when the code fails as tell as what line the code failed on and we might be able to help you.

Re: Delete Worksheet If It Exists

hmm... thats really weird.

Did you just try the example I posted or did you copy and paste the code and button to your actual workbook?

What excel version are you using?

I just cannot understand why even the button didnt work it just has a link to the code....

Re: Delete Worksheet If It Exists

Sheets.Add.Name = "Cleaned"

I don't think that's valid syntax. How about instead

Sheets.Add ' (Before:= or After:= can specify where)
ActiveSheet.Name ="Cleaned"

Re: Delete Worksheet If It Exists

Interesting; I don't see that syntax in the documentation, but it's neat that it works.

Peculiar that you can't delete the sheet, though. Can you delete it from the Immediate window? From the user interface? Is the sheet or workbook protected?

Re: Delete Worksheet If It Exists

Hmmm. You put a breakpoint on the line of code that does the deletion, step to watch it execute the line, and the worksheet just stays there fat and happy?

Did you say that you can do

Worksheets("Cleaned").Delete

successfully from the Immediate window?

Re: Delete Worksheet If It Exists

Cavemen are now a protected species under the political correctness code.

The Immediate window is where debug.print outputs appear. You can also type VBA code into the window and execute it. So type,

Worksheets("Cleaned").Delete

into it and see if it works.

Do you know how to set a breakpoint and step though code?

Re: Delete Worksheet If It Exists

OK, open the VB editor, put the cursor on the line of code that's supposed to delete the worksheet, and press F9 to set a breakpoint. You'll see that the line highlights in some color according to your editor settings.

Then do whatever you do to make the code run. Execution will halt before executing that line. Pressing F8 then executes code one line at a time. It's the process you use for stepping through code.

So the question is, do you hit the breakpoint? And what happens when you execute the line?

Re: Delete Worksheet If It Exists

Also note that putting an apostrophe in front of any lines of code disables that line from running until the apostrophe is removed.

You might want to add like below when debugging:

'On Error Resume Next 
'Application.DisplayAlerts = False 
ThisWorkbook.Worksheets("Cleaned").delete 
'Application.DisplayAlerts = True 
'On Error Goto 0

This will not allow the code to "run over" any errors and also display alerts.

Re: Delete Worksheet If It Exists

So it is working now?

Re: Delete Worksheet If It Exists

Hmm. I have no idea how to trigger an event in Personal.xls from another workbook. You could call the routine in Personal from a change event in the workbook of interest, but then you might as well put the rest of the code in there -- plus no one else has your Personal.

Let us know how you do.