How to print a sheet based on criteria of a group of cells

[rickadams]

Hi,
I am attempting to print volunteer sign in sheets based on active volunteers and not print the sheets for inactive ones. The organization can have up to 50 volunteers on the roster for bi weekly time and may only have 6-10 active during a given 2 week period. We need to track their hours because some are here for community service and some are here for CEU time.
If a particular volunteer is on the schedule I click a box next to their name with a “Y” and not active I enter “N”. I only want the 2 week sheets (all on 1 page) to print if the Active is “Y”.
First name stored in K12:K65, last Names are stored L12:L65 in a sort-able list Active or inactive criteria is stored in cells O12:O65
Each sheet populates with their first and last name and id number and the time sheet is now manually printed by sequence number.
[TABLE="width: 426"]
<tbody>[TR]
[TD]First[/TD]
[TD]Last[/TD]
[TD][/TD]
[TD]ID #[/TD]
[TD]Active[/TD]
[TD]SEQUENCE[/TD]
[/TR]
[TR]
[TD]JANE[/TD]
[TD]DOE[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]Y[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]NOT [/TD]
[TD]HERE[/TD]
[TD][/TD]
[TD]126[/TD]
[TD]N[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
ETC



















[TABLE="width: 617"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"] Bi Weekly Time Sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

<tbody>
</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name:[/TD]
[TD="colspan: 2"]JANE[/TD]
[TD="colspan: 2"]DOE[/TD]
[TD][/TD]
[TD]Volunteer Number[/TD]
[TD]125[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"] Other Period [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 1: [/TD]
[TD][/TD]
[TD="colspan: 3"]January 21, 2019[/TD]
[TD="colspan: 4"]January 27, 2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Morning Hours[/TD]
[TD="colspan: 2"]Afternoon Hours[/TD]
[TD]Total Hours[/TD]
[TD="colspan: 2"]Office Use Only[/TD]
[/TR]
[TR]
[TD]ROUND TO 1/4 HOUR[/TD]
[TD][/TD]
[TD]Time In [/TD]
[TD]Time Out[/TD]
[TD]Time In [/TD]
[TD]Time Out[/TD]
[TD][/TD]
[TD]Regular [/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1/21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]1/22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1/23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]1/24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]1/25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]1/26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]1/27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]Time sheet must be filled out daily.[/TD]
[/TR]
[TR]
[TD] Week 2 :[/TD]
[TD][/TD]
[TD="colspan: 3"]January 28, 2019[/TD]
[TD="colspan: 4"]February 3, 2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Morning Hours[/TD]
[TD="colspan: 2"]Afternoon Hours[/TD]
[TD]Total Hours[/TD]
[TD="colspan: 2"]Office Use Only[/TD]
[/TR]
[TR]
[TD]ROUND TO 1/4 HOUR[/TD]
[TD][/TD]
[TD]Time In [/TD]
[TD]Time Out[/TD]
[TD]Time In [/TD]
[TD]Time Out[/TD]
[TD][/TD]
[TD]Regular [/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]1/28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]1/29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]1/30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]1/31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]2/1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]2/2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]2/3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Hours Total – Week 1 and 2[/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Signatures[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Volunteer [/TD]
[TD]Date[/TD]
[TD="colspan: 3"]Department Supervisor[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Supervisor [/TD]
[TD]Date[/TD]
[TD="colspan: 3"]Other Department[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="colspan: 3"]TIME SHEET MUST BE SIGNED.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you for your help.

 

[CatyH]

I think what I would do would be to
1. create an index / roster of the ACTIVE ones, then
2. make that named list a data validation (drop down) cell that you can reference via VBA code.

Here is some code I used once that printed a set of pdfs based on a Data-Validation (drop down list) in cell E3 of NameOfSheet). Hope this helps!

Sub NameOfSub()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range


Application.ScreenUpdating = False
'''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then
FolderName = .SelectedItems(1) & ""
Else
Exit Sub
End If
End With


'''' Location of DataValidation cell
Set r = Worksheets("NameOfSheet").Range("e3")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)


'''' Loop through DataValidation list
For Each c In inputRange

If c.Value = "" Then
c.Value = "NoProgramName"
Else
End If

r.Value = c.Value
fName = c.Value
'''' Save as pdf
Sheets("Dashboard").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next c
Application.ScreenUpdating = True
End Sub

Hi,
I am attempting to print volunteer sign in sheets based on active volunteers and not print the sheets for inactive ones.

 

[rickadams]

CatyH, I feel bad because I did not thank you For some reason I did not see your response until I looked up my posts. Thank you so much I will try your suggestion!

 

[CatyH]

CatyH,...

No worries - probably just a setting somewhere. Happens to me all the time on my cell but do let me know how it works - someone on this forum helped me with that very code (and more recently with a different conundrum) so I like to help if I can. This forum is an amazing place to swap ideas and get help!