Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a sheet via code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a sheet via code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a sheet via code
A slight oversight, but probably a waste of time correcting it:
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(sh.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 -- Regards, Tom Ogilvy "KimberlyC" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a sheet via code
Try this version:
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(sh.Range("F9:B*H1000")) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting row using vb code? | Excel Discussion (Misc queries) | |||
Deleting the area code on excel spread sheet phone numbers?? | Excel Discussion (Misc queries) | |||
VBA Code for Deleting a Row | Excel Worksheet Functions | |||
Deleting Code from VBA | Excel Discussion (Misc queries) | |||
Deleting code in a file with code.. | Excel Programming |