Printing a filtered range one at a time

**andyb63uk** · 11-26-2011, 08:42 AM

I often have to print out some A4 sheets with the names of a users so we can put them on the dressing doors they will use.

I would like one button that filters and prints the dressing room name and user name on an A4 landscape sheet as per the 'Printout' tab on the attached sheet. But I don't want it to print those rooms not in use. eg On the 'Info' tab, there are only 4 rooms being used so only 4 sheets of paper to print.

Is it possible?

**Domski** · 11-26-2011, 10:54 AM

Try this:

Sub test()
    Dim rngLoopRange As Range
    With Sheets("Info")
        With .Range("A4").CurrentRegion
            For Each rngLoopRange In .Resize(.Rows.Count - 1, 1).Offset(1, 1)
                If rngLoopRange.Value <> "" Then
                    Sheets("Printout").Range("A3") = rngLoopRange.Offset(0, -1)
                    Sheets("Printout").Range("A4") = rngLoopRange
                    Sheets("Printout").PrintOut
                End If
            Next rngLoopRange
        End With
    End With
End Sub

Dom

**andyb63uk** · 11-26-2011, 06:54 PM

Many thanks for that. I should have mentioned on the example sheet to only print the range. Your code continues down the whole sheet. the actual range of cells is A55 and B55 to A66 and B66 on the HUDDERSFIELD sheet, I have called that range 'DressingRooms' (if that helps). So could it stop at A66 and B66?

Whilst I'm at it, is there a way to create folders\sub folders determined by a date in a field, if they dont already exist. eg Date entered in cell B8 as dd/mm/yy but displayed as ddd dd mm yy. file to be exported as G:\Town Halls\Event Sheets\sheet1 name\yyyy\mm mmm\dd.mm.yy - name of event.pdf

my current code for this is

Private Sub CommandButton1_Click()
    
    Dim newFile As String, fName As String
    fName1 = Range("B8").Value
    fName2 = Range("B9").Value
    newFile = fName1 & " - " & fName2 & ".pdf"

    With Sheet1
        .AutoFilterMode = False
        .Range("D1").AutoFilter
        .Range("D1").AutoFilter Field:=4, Criteria1:=1
    
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "G:\Town Halls\Event Sheets\Huddersfield\" & newFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        
        .Range("D1").AutoFilter
    End With

End Sub

**Domski** · 11-27-2011, 03:57 AM

What Huddersfield sheet?

Please edit your post to include code tags per the forum rules.

Dom

**andyb63uk** · 11-27-2011, 04:43 AM

Sorry, I'm new to all this.

I attached an example xls to demo what I wanted to do, I didnt know if I could send the Actual xlsm. how do I attach in a private message?

Andy

**Domski** · 11-27-2011, 05:36 AM

You can't add workbooks to messages, only to threads.

Please still edit your post to include the tags.

Dom

**andyb63uk** · 11-27-2011, 06:36 AM

OK done that and attached the actual file being used.

Row 68 on Huddersfield sheet is where the button is and it only needs to print between A55 and A66.
Oh and the other thing I mentioned about the save as PDF is the Publish button at the bottom of Huddersfield

I'm really grateful for this.

Andy

**andyb63uk** · 11-30-2011, 12:59 PM

How can I get the following to STOP at cell A66, as it carries on for the whole sheet. I only want it to print between A55 and A66

Private Sub CommandButton2_Click()
'prints door sheets for dressing room users
    Dim rngLoopRange As Range
    With Sheets("Huddersfield")
        With .Range("A55").CurrentRegion
            For Each rngLoopRange In .Resize(.Rows.Count - 1, 1).Offset(1, 1)
                If rngLoopRange.Value <> "" Then
                    Sheets("Printout").Range("A3") = rngLoopRange.Offset(0, -1)
                    Sheets("Printout").Range("A5") = rngLoopRange
                    Sheets("Printout").PrintOut
                End If
            Next rngLoopRange
        End With
    End With
End Sub