Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Specific Sheets
Hi,
I want to delete specific sheets within a workbook. I want to delete those sheets which have a local range name (xsSheetType) set to either "Project" or "Summary". Not all sheets will have the range name. And many of those that do have the range name will have a value other than "Project" or "Summary". I tried my routine below. I know there is something wrong with this line: If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then When I hover over the left hand side, I will see wsSheet.Range(sSheetType).Value = "Project" yet it hits the error handler. This puzzles me. I think I am very close, but missing something simple. If there is a better method of writing this subroutine, I'd like to know. But I am also very curious what I did wrong. Any help is appreciated. Regards, Kevin Sub DeleteOldSheets() Dim wsSheet As Worksheet '\ Names of sheets to be deleted Dim sSheetName() As String Dim iCounter1 As Integer Dim iCounter2 As Integer dim sSheetType as String sSheetType = "xsSheetType" iCounter1 = 0 ReDim sSheetName(1 To Worksheets.Count) For Each wsSheet In Worksheets '\ In case sheet doesn't have the specified range On Error GoTo Err1 If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then iCounter1 = iCounter1 + 1 sSheetName(iCounter1) = wsSheet.Name LabelResume1: End If Next wsSheet If iCounter1 = 0 Then Exit Sub ReDim Preserve sSheetName(1 To iCounter1) Application.DisplayAlerts = False For iCounter2 = 1 To iCounter1 Worksheets(sSheetName(iCounter2)).Delete Next iCounter2 Application.DisplayAlerts = True Err1: On Error GoTo 0 Resume LabelResume1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Specific Sheets
This might be the culprit.
If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then Try: If wsSheet.Range(sSheetType).Value = "Project" Or _ wsSheet.Range(sSheetType).Value = "Summary" Then "Kevin H. Stecyk" wrote: Hi, I want to delete specific sheets within a workbook. I want to delete those sheets which have a local range name (xsSheetType) set to either "Project" or "Summary". Not all sheets will have the range name. And many of those that do have the range name will have a value other than "Project" or "Summary". I tried my routine below. I know there is something wrong with this line: If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then When I hover over the left hand side, I will see wsSheet.Range(sSheetType).Value = "Project" yet it hits the error handler. This puzzles me. I think I am very close, but missing something simple. If there is a better method of writing this subroutine, I'd like to know. But I am also very curious what I did wrong. Any help is appreciated. Regards, Kevin Sub DeleteOldSheets() Dim wsSheet As Worksheet '\ Names of sheets to be deleted Dim sSheetName() As String Dim iCounter1 As Integer Dim iCounter2 As Integer dim sSheetType as String sSheetType = "xsSheetType" iCounter1 = 0 ReDim sSheetName(1 To Worksheets.Count) For Each wsSheet In Worksheets '\ In case sheet doesn't have the specified range On Error GoTo Err1 If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then iCounter1 = iCounter1 + 1 sSheetName(iCounter1) = wsSheet.Name LabelResume1: End If Next wsSheet If iCounter1 = 0 Then Exit Sub ReDim Preserve sSheetName(1 To iCounter1) Application.DisplayAlerts = False For iCounter2 = 1 To iCounter1 Worksheets(sSheetName(iCounter2)).Delete Next iCounter2 Application.DisplayAlerts = True Err1: On Error GoTo 0 Resume LabelResume1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Specific Sheets
"JLGWhiz" wrote in message
If wsSheet.Range(sSheetType).Value = "Project" Or "Summary" Then Try: If wsSheet.Range(sSheetType).Value = "Project" Or _ wsSheet.Range(sSheetType).Value = "Summary" Then Yes, that solved my problem. Thank you! Best regards, Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update data from multiple sheets to one specific sheets | Excel Discussion (Misc queries) | |||
prevent user to delete specific sheets | Excel Discussion (Misc queries) | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |