Filter & Print

[Guest]

Would like a macro that would Filter the rows by Col I and then print all the
data based on Col I
Col A = No. Col F = Place
Col B = Name Col G = Best Time
Col C = 1st Run Col H = Club
Col D = 2nd Run Col I = Age Group
Col E = 3rd Run

I have an auto filter on Col I that I select an age group then print that
page then select another Age Group and print again etc. What I would like to
do is run a VBA on a keyboard assign key that would print all the data based
on all the different age groups for that worksheet.
I have 12 plus worksheets and each sheet can have 150 plus rows of data
The cells in Col I are based on a list.
Can email file for your info

Many thanks
Laurie g

 

[Bernie Deitrick]

Laurie,

Try the macro "DoPrintOuts", below.

HTH,
Bernie
MS Excel MVP

Sub DoPrintOuts()
Dim mySht As Worksheet
For Each mySht In ActiveWorkbook.Worksheets
PrintByFilter Intersect(mySht.UsedRange, mySht.Range("I:I"))
Next mySht
End Sub

Sub PrintByFilter(myRange As Range)
Dim myList() As Variant
Dim i As Integer
Dim j As Integer
Dim mycount As Integer
mycount = 1
With myRange
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
ReDim myList(1 To .SpecialCells(xlCellTypeVisible).Count)
With .SpecialCells(xlCellTypeVisible)
For j = 1 To .Areas.Count
For i = 1 To .Areas(j).Cells.Count
myList(mycount) = .Areas(j).Cells(i).Value
mycount = mycount + 1
Next i
Next j
End With
myRange.Parent.ShowAllData
End With

For i = LBound(myList) + 1 To UBound(myList)
myRange.AutoFilter Field:=1, Criteria1:=myList(i)
myRange.Parent.PrintOut
Next i

End Sub