vba to print a selection of sheets?

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,388
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....
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,626
Messages
5,523,981
Members
409,550
Latest member
baaabies

This Week's Hot Topics

Top