PDF Save as
 

I am using the following code to print each unique sheet in a workbook but
would like to link to a cell for a filename rather than having to type this
information in, the code i use is as follows;

Sub PrintAllSheets()
'
Dim M As Long, N As Long, Firstsht As Long, Lastsht As Long, Sheet As
Object
'
Lastsht = Sheets.Count
M = 0: N = Lastsht
'
For Each Sheet In Sheets
If Not Sheet.Visible Then N = N - 1
If Sheet.Visible And Sheet.Type = xlWorksheet Then
If WorksheetFunction.CountA(Sheet.UsedRange) = 0 Then
N = N - 1
End If
End If
Next
'
For Firstsht = 1 To Lastsht
'
If Sheets(Firstsht).Visible = True Then
'
If Not TypeName(Sheets(Firstsht)) = "Chart" Then
If WorksheetFunction.CountA(Sheets(Firstsht).UsedRang e) < 0
Then
M = M + 1
GoSub DoPrint
End If
Else 'else it's a chart
M = M + 1
GoSub DoPrint
End If
'
End If
'
Next 'Firstsht
Exit Sub
DoPrint:
With Sheets(Firstsht).PageSetup
.CenterHeader = "&F!&A"
.CenterFooter = "Page " & CStr(M) & " of " & N
.RightFooter = "©" & CStr(Year(Date))
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With Application
.EnableEvents = False
Sheets(Firstsht).PrintOut
.EnableEvents = True
End With
Return
End Sub