Can't delete worksheet

[Noz2k]

Am getting an error message when trying to delete a worksheet in VBA, wondering where I'm going wrong in the code and/or what might be preventing it from working.

here is my code

Workbooks("Data").Save
Workbooks("without Data").Sheets("Sheet1").Unprotect "Password"
Workbooks("without Data").Sheets("MainMenu").Activate
Application.DisplayAlerts = False
Workbooks("without Data").Sheets("Sheet1").Delete
Application.DisplayAlerts = True
Workbooks("Data").Sheets("Sheet1").Copy Before:=Workbooks("without Data")
Workbooks("Data").Close

 

[Noz2k]

Still can't get around this problem.

Have tried renaming the sheet as "sheet50" copying in the new sheet as "sheet1" then deleting "sheet50" but it still doesn't work.

It renames, and copies in, but doesn't delete.

Could it have anything to do with there being an activex control on the page?

 

[VoG]

I've tried with an ActiveX control on the sheet and no error.

 

[Noz2k]

Solved it!

Had to activate the worksheet first, apparently it will only let me delete the worksheet if that's the one in view

Workbooks("Data").Save
Workbooks("without Data").Sheets("Sheet1").Unprotect "Password"
Application.DisplayAlerts = False
Workbooks("without Data").Sheets("Sheet1").Activate
Workbooks("without Data").Sheets("Sheet1").Delete
Application.DisplayAlerts = True
Workbooks("Data").Sheets("Sheet1").Copy Before:=Workbooks("without Data")
Workbooks("Data").Close

No idea why that's the case, because it wasn't when I opened a new workbook and tried to delete a sheet in there (to check that it wasn't to do with the security settings) because that worked fine deleting sheet3 when sheet1 was the active sheet

 

[villy]

I don't really understand why you need to unprotect the sheet if it isn't protected at first?
It could be the reason though because it is somehow protected because I don't see anything wrong with your code.
Just my thoughts.
Thanks

 

[villy]

This is really strange.. It doesn't matter if your VB code is protected I used to do that in all my VB codes and its working fine even deleting a worksheet(s).
i know you cannot delete a worksheet only if there is only one (you cannot really delete sheet1) and if it is protected as VoG said.

Have you tried to rename it before deleting?

 

[Noz2k]

Yes, I have tried renaming it.

I thought I fixed it yesterday, but that's wasn't the case, I only got it working in the immediate window.

I have now removed the display alerts lines and it brings up the prompt to delete and then deletes fine if you click yes.

So I'm assuming the problem is with the way my code currently handles the alert, any way around this?