delete sheet
 

You could use code like this to delete your worksheet and any Names
referencing it...

Sub DeleteSheetAndItsNames()
Dim N As Name
Dim WS As String
WS = "Sheet3"
For Each N In ThisWorkbook.Names
If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
Next
Worksheets(WS).Delete
End Sub

Just change the worksheet name reference or, better still maybe, make it a
Variant argument to the subroutine (then you can specify the worksheet name
or number) so that your code can call it and specify the worksheet name or
number when doing so. You can then, of course, bolster it up with some error
checking.

Rick


"ranswrt" wrote in message
...
I have a procedure to delete a sheet in a workbook. All the named cells in
that sheet gets deleted. When I use the 'F3' to paste the list of named
cells, the cells in the sheet that was deleted appear in it. Do I need to
delete the named cells before I delete the sheet?

Thanks

delete sheet
 

Well, actually, if you changed it to a Variant argument, you would have to
adapt the If-Then statement to account for that. Something like this
maybe...

Sub DeleteSheetAndItsNames(WS As Variant)
Dim N As Name
Dim S As Worksheet
If WS Like String(Len(WS), "#") Then
If WS <= Worksheets.Count Then
WS = Worksheets(WS).Name
End If
End If
For Each S In Worksheets
If S.Name = WS Then
For Each N In ThisWorkbook.Names
If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
Next
Worksheets(WS).Delete
Exit Sub
End If
Next
MsgBox "No such worksheet!", vbCritical, "No Such Worksheet"
Err.Raise 1111, , "No such worksheet!"
End Sub

I provided a trappable error that can be caught by your calling code. For
example...

Sub Test()
On Error GoTo Whoops
DeleteSheetAndItsNames "SheetX"
Exit Sub
Whoops:
Debug.Print Err.Number, Err.Description
End Sub

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
You could use code like this to delete your worksheet and any Names
referencing it...

Sub DeleteSheetAndItsNames()
Dim N As Name
Dim WS As String
WS = "Sheet3"
For Each N In ThisWorkbook.Names
If Mid(Split(N.RefersTo, "!")(0), 2) = WS Then N.Delete
Next
Worksheets(WS).Delete
End Sub

Just change the worksheet name reference or, better still maybe, make it a
Variant argument to the subroutine (then you can specify the worksheet
name or number) so that your code can call it and specify the worksheet
name or number when doing so. You can then, of course, bolster it up with
some error checking.

Rick


"ranswrt" wrote in message
...
I have a procedure to delete a sheet in a workbook. All the named cells
in
that sheet gets deleted. When I use the 'F3' to paste the list of named
cells, the cells in the sheet that was deleted appear in it. Do I need
to
delete the named cells before I delete the sheet?

Thanks