M
Mark
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
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