Hi,
I would like to print a header (rows 1-9) with each row from the Autofilter list on a separate page. I have written the code below but this is only printing the header (rows 1-9).
Can anyone help?
Many thanks,
Karla
Code
Private Sub PrintButton_Click()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
Application.ScreenUpdating = False
Set Sh = Worksheets("Y11 AR")
Set Rng = Sh.Range("A9:A" & Sh.Range("A65536").End(xlUp).Row)
On Error Resume Next
For Each c In Rng
List.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
With Sh.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
End With
Set Rng = Sh.Range("A9:A" & Sh.Range("A65536").End(xlUp).Row)
For Each Item In List
Rng.AutoFilter , Field:=4, Criteria1:=Item
ActiveSheet.AutoFilter.Range.PrintOut
Next Item
Application.ScreenUpdating = True
End Sub