| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select/Delete Sheets
Thanks, Dave! That worked like a charm.
Mark "Dave Peterson" wrote: You could use the position of the sheet within the workbook, but I wouldn't. If "1st Grade" is moved all the way to the right, then it would screw everything up. Instead (and you may not like it), I'd change the names of the worksheets... Kindergarten (no change) Grade 1 (instead of 1st grade) Grade 2 (instead of 2nd grade) and so forth... (I'd even use Grade 0 for kindergarten, but that doesn't look so nice.) Then you could parse the sheet based on the option button. =========== But since your list of sheets is pretty small, you could do something like: Option Explicit Sub Insert_Sheet_Template() Dim sh As Worksheet Dim ShToKeep As Variant Dim tempPath As String Dim res As Variant Dim NewWkbk As Workbook 'don't you need the backslash in the next line??? tempPath = Application.TemplatesPath & "\" & "ShToKeepTemplates.xltm" Application.ScreenUpdating = False Set NewWkbk = Application.Workbooks.Add(Template:=tempPath) With EntryForm Select Case True Case .optGradeK ShToKeep = Array("control", "Kindergarten") Case .optGrade1 ShToKeep = Array("control", "Kindergarten", "1st Grade") Case .optGrade2 ShToKeep = Array("control", "1st Grade", "2nd Grade") Case .optGrade3 ShToKeep = Array("control", "2nd Grade", "3rd Grade") Case .optGrade4 ShToKeep = Array("control", "3rd Grade", "4th Grade") Case .optGrade5 ShToKeep = Array("control", "4th Grade", "5th Grade") Case Else 'do nothing End Select End With For Each sh In NewWkbk.Worksheets res = Application.Match(sh.Name, ShToKeep, 0) If IsError(res) Then 'not in the array, delete it Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If Next sh End Sub Tested, but without the template file--just against the active workbook. Mark wrote: Hi, I have a workbook with 7 sheets in it. I will always need Sheet1(€œControl€) and only two of the remaining sheets, based upon what the User selects via a user form. If the User selects €œFifth Grade€, Sheet7, then I will need to display the previous sheet, too (Sheet6(€œFourth Grade€)). Thus, the final workbook will contain Sheets 1, 6, and seven. The remaining sheets will be deleted. My question is, how do I select the previous sheet and sheet1 when I loop through the worksheets? Thanks. Mark Sub Insert_Sheet_Template() Dim sh As Worksheet Dim grade As String Dim tempPath As String tempPath = Application.TemplatesPath & "GradeTemplates.xltm" Application.ScreenUpdating = False Application.Workbooks.Add Template:=tempPath With EntryForm Select Case True Case .optGradeK grade = "Kindergarten" Case .optGrade1 grade = "1st Grade" Case .optGrade2 grade = "2nd Grade" Case .optGrade3 grade = "3rd Grade" Case .optGrade4 grade = "4th Grade" Case .optGrade5 grade = "5th Grade" Case Else End Select End With For Each sh In ActiveWorkbook.Worksheets If sh.Name < grade Then sh.Delete End If Next ActiveSheet.Name = "Fall" ' ActiveSheet.Previous.Name = "Last Spring Scores" Application.ScreenUpdating = True End Sub -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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 | |||
| Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
| select and delete sheets | Excel Programming | |||
Threaded Mode
