Hi again,
Many thanks for your very prompt response to my last post.
Take a look at the attached workbook and see if it does what you need. The workbook provides the facilities to sort team member names (as per your previous thread), print a hardcopy listing of all team details, and generate a PDF file containing one page per team. It uses the following code:
Option Private Module
Option Explicit
'=========================================================================================
'=========================================================================================
Const msSHEET_TO_EXCLUDE__1 As String = "ReadMe"
Const msSHEET_TO_EXCLUDE__2 As String = "Sheet5"
Const msSHEET_TO_EXCLUDE__3 As String = "Sheet6"
'=========================================================================================
'=========================================================================================
Public Enum OutputType
HardCopy = 1
PDF
End Enum
'=========================================================================================
'=========================================================================================
Sub SortTeamMemberNames()
Const sRANGE_TO_SORT As String = "A3:B25"
Dim rRangeToSort As Range
Dim wks As Worksheet
For Each wks In Worksheets
With wks
If .Name <> msSHEET_TO_EXCLUDE__1 And _
.Name <> msSHEET_TO_EXCLUDE__2 And _
.Name <> msSHEET_TO_EXCLUDE__3 Then
Set rRangeToSort = .Range(sRANGE_TO_SORT)
With rRangeToSort
.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
End With
End If
End With
Next wks
End Sub
'=========================================================================================
'=========================================================================================
Sub PrintWorkbook()
Call GenerateOutput(eOutputType:=HardCopy)
End Sub
'=========================================================================================
'=========================================================================================
Sub ExportWorkbookToPDF()
Call GenerateOutput(eOutputType:=PDF)
End Sub
'=========================================================================================
'=========================================================================================
Sub GenerateOutput(eOutputType As OutputType)
Const sPDF_FILENAME As String = "Team Details.pdf"
Dim sFullName As String
Dim wksActive As Worksheet
Set wksActive = ActiveSheet
Call WorksheetsAreVisible(bTrueOrFalse:=False)
If eOutputType = HardCopy Then
ThisWorkbook.PrintOut Preview:=True
Else: sFullName = ThisWorkbook.Path & "\" & sPDF_FILENAME
ThisWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sFullName, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End If
Call WorksheetsAreVisible(bTrueOrFalse:=True)
wksActive.Activate
End Sub
'=========================================================================================
'=========================================================================================
Private Sub WorksheetsAreVisible(bTrueOrFalse As Boolean)
With ThisWorkbook
.Worksheets(msSHEET_TO_EXCLUDE__1).Visible = bTrueOrFalse
.Worksheets(msSHEET_TO_EXCLUDE__2).Visible = bTrueOrFalse
.Worksheets(msSHEET_TO_EXCLUDE__3).Visible = bTrueOrFalse
End With
End Sub
The highlighted values may be altered to suit your requirements.
As far as:
Each Tab requires same format of -
A4 Landscape, Page Margins (as per custom setting on each Tab in this WB);
Print Range A4 : M24 - this is final area intended to be used
is concerned, you can set all of these parameters on the template worksheet which you're using for each team. One piece of advice which (based on sad experience!) I can offer, is to make sure that your template worksheet is fully and correctly configured BEFORE you start making copies of it and entering team details, otherwise you'll end up having to perform "tweaks" on each of 109 separate worksheets afterwards!
Hope this helps - as before, please let me know how you get on.
Regards,
Greg M
Bookmarks