Deleting a sheet via code
Hi Tom,
Thanks for your help..
I tried this..and here's what happened.
If I have AC Details and AC Details (2) in the workbook, and there is data
in cells F9:BH1000 of the first AC Details worksheet, and nodata in the AC
Details (2), then it doesn't prompts to delete the second sheet...that has
nodata in it.
Also.. if
If neither sheets have data in them and I'm not on either sheet (when I run
the code).. as the activesheet, the code does not prompt to delete either of
them.
If neither sheets have data in them and I'm on the one of the sheets as the
activesheet (AC Details or AC Details (2)), then it prompts me to delete
both..
"Tom Ogilvy" wrote in message
...
Sub ABB()
Dim sh As Worksheet
Dim cnt As Long
Dim ans As Long
For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "AC Details", vbTextCompare) 0 Then
cnt = Application.CountA(Range("F9:BH1000"))
If cnt = 0 Then
ans = MsgBox("Delete Sheet Name " & sh.Name, vbYesNo)
If ans = vbYes Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
End If
End If
Next
End Sub
worked fine for me.
--
Regards,
Tom Ogilvy
"KimberlyC" wrote in message
...
Hi
I need to run code that checks all of the worksheets with the name " AC
Details" in my activeworkbook (there can be many sheets with this name
for
ex: AC Details (2), AC Details (3) and so on...) to see if any data has
been entered into cells F9:BH1000.
If no data has been entered into those cells, then I need the code to
ask
the user if they want to delete the sheet, and if they say yes...then
delete
the sheet..
if they say no..then do not delete the sheet.
If the code finds that data has been entered into those cells
(F9:BH1000)..
then I need the code to not delete the sheet (or do nothing).
Any help in doing this woud be greatly appreciated.
Thanks in advance for your help..
Kimberly
|