VBA create form and save file of specific sheets

[missbb9413]

I have a VBA code to create staff form. there are two problems need to be solved.
Q1 : after creating sheets, the sheets will be saved as file. I only want to save the sheet(7) to sheets.count ? (i.e. sheet (1) to sheet (6) is not required to save file. but below code will save all sheets. Please help!

Sub copusheettofile1()


For Each sh In Worksheets

sh.Copy

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & sh.Name & ".xlsx"
Workbooks(sh.Name & ".xlsx").Close True

Next

End Sub




Q2 : I have included Application.ScreenUpdating = False and Application.DisplayAlerts = False to below code, but I always see some screenupdating image , is there anything wrong ?


Sub copytosheetok02()
'step select dept -> create appraisal form based on sheet result


Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Sheets("list").Activate


Dim yn As Integer
Dim formname As String
Dim dept As String
Dim DDate As Long




yn = MsgBox(prompt:="filter is Dept press YES;filter is join date press NO ", Buttons:=vbYesNo + vbQuestion)
If yn = vbYes Then


On Error Resume Next

dept = Sheets("menu").Range("e9").Value
Sheets("list").Range("a1").AutoFilter Field:=7, Criteria1:=dept
ActiveSheet.UsedRange.Select
Selection.Copy
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "result"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("result").Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[3]&""""&RC[-1]"
Selection.AutoFill Destination:=Range("b2:b" & Range("A" & Rows.Count).End(xlUp).Row)


Else


DDate = Sheets("menu").Range("e13").Value
Sheets("list").Range("a1").AutoFilter Field:=6, Criteria1:=">" & DDate
ActiveSheet.UsedRange.Select
Selection.Copy
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "result"
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("result").Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[3]&""""&RC[-1]"
Selection.AutoFill Destination:=Range("b2:b" & Range("A" & Rows.Count).End(xlUp).Row)

End If


End With


Sheets("menu").Activate


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub