Printing with filters

[evanhughes]

Hi All

I'm new to this forum so please bear with me.
I have searched the forum but can't seem to get what I need.

I have a spread sheet (a specification) which has a number of filters.

By selecting the relevant filters I can print the section that refers to that dept.

What I need is a VBA that will allow me to select the depts. and it print out the filtered selection. If it could have check boxes to select multiple depts and print them each individually that would be great. I can set the the filters that pertain to each department in the vba as they will not change.

Any help appreciated.

Evan

[Palmetto]

Since you didn't provide any details about or a sample of your workbook, here is a basic working example.

Instead of check boxes, I have used Marlett "check boxes". Marlett is a font whose "a" character is checkmark. Using VBA code we can turn this checkmark on and off to indicate selection. In the attachment, click in cells B2:B4 to see this in action.

Here is the code in the attached workbook. Obviously, you will have to adapt it per your actual workbook.
In the worksheet module:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("B2:B4")) Is Nothing Then
        Target.Font.Name = "Marlett"
        If Target = vbNullString Then
            Target = "a"
        Else
            Target = vbNullString
        End If
    End If

End Sub

In a standard module and assigned to a button on the sheet:

Option Explicit

Sub Filter_andP_Print()

    Dim c As Range, rPrintArea

    Application.ScreenUpdating = False
    Set rPrintArea = Sheet1.Range("a7").CurrentRegion.SpecialCells(xlCellTypeVisible)
    
    With Sheet1
        If .Range("B1").Value = "" And _
            .Range("B2").Value = "" And _
            .Range("B3").Value = "" Then
            MsgBox ("No filter selections are chosen - nothing to do!"), vbExclamation
            Exit Sub
        End If
        .AutoFilterMode = False
        .PageSetup.PrintArea = ""
        .Range("A7").CurrentRegion.AutoFilter
        On Error Resume Next
        
        For Each c In .Range("B2:B4")
            Select Case c.Value
                Case "a"
                    .Range("A7:B7").AutoFilter Field:=1, Criteria1:=c.Offset(0, -1).Value
                    .PageSetup.PrintArea = rPrintArea.Address
                    .PrintOut
            End Select
        Next c
        .AutoFilterMode = False
        .Range("B2:B4").ClearContents
        .PageSetup.PrintArea = ""
    End With
    
    Application.ScreenUpdating = True
    
End Sub

[evanhughes]

Thanks for the speedy reply.

I will post a sample of the workbook and what needs to print for the depts on Monday as I need to get to work to access.

Hope you will be able to assist.

Evan

[evanhughes]

Have attached sample xls.

Here is a list of the Depts and what filter of the sheet they need printed.

Dept. - Spec. Filter

Main spec - General Information*

Mill - Fitted Furniture

Floors - Floor

Electricians - Electric

Plumbers - Plumbing

Cab Shop - Fitted Furniture
- Plumbing
- Electric

Furnishers - Fitted Furniture

Decorators - Ceiling & Wall

Tiling & Dressing - Tiling
- Soft Furnishing


Hope this is possible

Evan