Delete a worksheet

[Guest]

Hello,

I'm using Excel 2003. I'm having trouble deleting a worksheet with VBA.
The code looks fine, even used MSDN to find some examples:

Worksheets("Sheet2").Delete

But it's not working. I open an Excel file in VBA, I add a sheet, copy data
from the sheet to another sheet, then delete the sheet, and save and close
the workbook. All of this is done with VBA. When I open up the workbook
manually, the sheet I thought I just deleted is still there. The copy and
paste are working fine.

Is there some prep-work that might need to take place before Excel allows
you to delete a sheet?

Any ideas?

Thanks,

Scott

 

[Guest]

That code is just fine. Post the rest of your code so that we can see what
you are up to. As a guess your problem is in referencing the workbook that
you opened. By any chance do you have On Error Resume Next in your code. If
the sheet is not getting deleted then it should be throwing an error.
Additionally if it is going to delete it will generate and alert that you are
about to delete a sheet (unless you have turned DispayAlerts off)...

 

[Guest]

hi,


I hope this will help to expand your code...

Sub CopyDataThenDelSht()
Dim ShtSource As Worksheet
Dim ShtDest As Worksheet

'setting variables sheet
Set ShtSource = Sheets("Sheet1")
Set ShtDest = Sheets("Sheet2")

'Copy data
ShtSource.Range("A1").Copy _
Destination:=ShtDest.Range("A1")

'turnoff msgbox delete confirmation
Application.DisplayAlerts = False
ShtSource.Delete
'turnon msgbox delete confirmation
Application.DisplayAlerts = True

'''''Then Save the workbook
'''It's important to make permanently deleted
'''make sure before you want to permanently delete
'ShtSource.Parent.Save
End Sub
..

That's all samples