I am making progress.
With the code below I can produce one or more sheets without sheets being deleted unnecessarily or extraneous sheets being left in the workbook. The only problem remaining is that everything below row 1 is copied from the first sheet in the user form list, not the sheet selected. Can anyone spot why the code is not looping through the sheets?
Private Sub CmdPrint_Click()
Dim DateString As String
Dim FolderName As String
Dim x As Long
Dim FileFormatNum As Long
Application.ScreenUpdating = False
DateString = Format(Now, "dd mmmm yyyy")
FolderName = "C:\Users\Alison\OneDrive - Royal Hospital School\Documents\Timetable WIP\2019-20\Staffing Grids for HoDs" & " " & DateString
For x = 0 To LstPrint.ListCount - 1
If LstPrint.Selected(x) = True Then
Application.CopyObjectsWithCells = False
With Sheets(LstPrint.List(x))
'If .ProtectContents = True Then
.Unprotect
.Copy Sheets(Sheets.Count)
'.Protect
'Else
'.Copy Sheets(Sheets.Count)
'End If
End With
Application.CopyObjectsWithCells = False
With Sheets(Sheets.Count)
'With Sheets(LstPrint.List(x))
.Unprotect
.Range("H1").Value = LstPrint.List(x)
.Range("A1").Value = .Range("A1").Value
.Range("B2:U4").Value = .Range("B2:U4").Value
.Range("E94:U104").Value = .Range("E94:U104").Value
.Copy
.Protect
End With
With ActiveWorkbook
.Sheets(Sheets.Count).Protect
.Sheets(Sheets.Count).Name = LstPrint.List(x)
If .HasVBProject Then
FileFormatNum = 52
Else
FileFormatNum = 51
End If
.SaveAs FolderName & "\" & LstPrint.List(x), FileFormatNum
.Close False
End With
Application.DisplayAlerts = False
Worksheets(LstPrint.List(x) & " (2)").Delete
Application.DisplayAlerts = True
End If
Next
'Application.DisplayAlerts = False
'Sheets(Sheets.Count).Delete
'Application.DisplayAlerts = True
Application.ScreenUpdating = True
Unload Me
Application.Goto Sheets("1. Staff List & Subjects").Range("A1")
Call Shell("explorer.exe " & FolderName, vbNormalFocus)
End Sub
Bookmarks