Sub b()
Dim fileSave As Variant
Dim msg As Integer
Dim sheetcount As Long
Dim name As String
Dim actvsheet As String
Dim tempname As String
Application.ScreenUpdating = False
actvsheet = ThisWorkbook.ActiveSheet.name
Set fileSave = Application.FileDialog(msoFileDialogSaveAs)
For i = 1 To 3
Sheets("Export Invoice").Select
Sheets("Export Invoice").Copy after:=Worksheets("Export Invoice")
tempname = tempname & _
ActiveSheet.name & ","
Sheets("Packing List").Select
Sheets("Packing List").Copy after:=Worksheets("Packing List")
tempname = tempname & _
ActiveSheet.name & ","
Next i
tempname = Left(tempname, Len(tempname) - 1)
sheetcount = Sheets.Count
For i = 2 To sheetcount
If i = sheetcount Then
''''''''''''''''''''Put the criteria to include the last sheet here''''''''''''''''''''''''''''''''
If ThisWorkbook.Sheets("DATA FILLER").Range("E51") = "YES" Then
name = name & _
ThisWorkbook.Sheets(i).name & ","
Else
End If
Else
name = name & _
ThisWorkbook.Sheets(i).name & ","
End If
Next i
If Len(name) > 1 Then
name = Left(name, Len(name) - 1)
Else:
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Sheets(Split(tempname, ",")).Delete
Sheets(actvsheet).Select
Application.DisplayAlerts = True
Exit Sub
End If
Sheets(Split(name, ",")).Select
'Prompts user with an are you sure message. Shows the name of the selected sheets.
msg = MsgBox("This was easy right? These documents will be printed as PDF " & Chr(10) & Replace(name, ",", Chr(10)), vbQuestion + vbOKCancel)
'If User choses ok proceeds with the printing.
If msg = vbOK Then
With fileSave
.InitialFileName = "Desktop\*.pdf"
'FilterIndex for a PDF file is 26 (You can count which row is a file type at when you Save As to get the desired file type's Index number.)
.FilterIndex = 26
'If user choses OK on the Save as screen.
If .Show = -1 Then
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
'If User choses cancel on the Save as screen.
Else
'Returns to the sheet that was active when the code was started (Makes sure multiple sheets are not still selected when the procedure is over.)
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Sheets(Split(tempname, ",")).Delete
Sheets(actvsheet).Select
Application.DisplayAlerts = True
Exit Sub
End If
End With
'If the user chooses Cancel to the msgBox, cancels the printing.
Else
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Sheets(Split(tempname, ",")).Delete
Sheets(actvsheet).Select
Application.DisplayAlerts = True
Exit Sub
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = False
Sheets(Split(tempname, ",")).Delete
Sheets(actvsheet).Select
Application.DisplayAlerts = True
End Sub