vba to print a selection of sheets?

[merlin777]

I have a book with 25 sheets which contain timetables for 25 people.

I want to select any or all of them and print them in one go.

I know I can click on tabs to group and print a selection but I have to make it as simple for my users as possible and I want them to be able to select yes/no in a box next to a list the names and then click on a button to print just those.

The other reason I can't use the tabs is they'll be called 'patient 1', 'patient 2' etc and not the actual names of the patients which might change from week to week and that makes it a bit tricky.

I already have my list of patient names and yes/no selection boxes. What I need is a little macro to print just those with yes beside them.

Names are in e3 to e27 on a sheet called 'admin' with the yes/no in f3 to f27. I will be printing to the default printer and using the print area selection on each sheet.

I tried to record a macro but there's no way to incorporate the yes/no selection....

 

[ParamRay]

.
.

Try running the following macro from your workbook. Please read through the code first and make any necessary adjustments.

Sub SheetsPrintOut()

    Dim Rang As Range
    Dim Num As Byte
    Dim Cell As Range
    Dim Arr() As String
    
    Set Rang = ThisWorkbook.Worksheets("Admin").Range("F3:F27")
    
    Num = 0
    For Each Cell In Rang
        If LCase(Cell.Value) = "yes" Then
            Num = Num + 1
            ReDim Preserve Arr(1 To Num)
            Arr(Num) = "patient " & Cell.Row - 2
        End If
    Next Cell
    
    If Num > 0 Then
        Sheets(Arr).PrintOut IgnorePrintAreas:=False
    End If

End Sub

 

[merlin777]

should there be an 'e' on rang?

 

[ParamRay]

should there be an 'e' on rang?

No, Rang is simply the name I've given to one of the Range variables. You can change the name if you want, but make sure you change all instances of it.