macro to print selection when there is data in rows

[bh24524]

Hi, so I have a sheet that does lookups to other tabs and displays data when someone is changing a schedule.
Here is a simplified format:

What we have to do is print out one sheet per person that is actually changing schedules so that each printed sheet displays the headers and only that persons name and the rest of the info printed to column H. For this sample we only go to column H for printing but what is in column I and beyond wouldn't get printed. What I currently do is hold down the ctrl button and then select from A to G those changing schedules and when I go to print it, it has each person separated on their own sheet and I print 2 copies. So base don the above sample data, I would have 3 separate pages - one for John Smith 1, one for John Smith 5, and one for John Smith 8.

My question is instead of me having to ctrl highlight the different selections I want to print is if there is a macro that could do this for me, so basically if columns F thru H have some type of data populating in them, it would print from A to H. So if I ran the macro according to the above screenshot sample, I would expect to see 3 separate pages:
Page 1

Page 2

Page 3

Again, printing excludes anything beyond column H.

 

[jolivanes]

Do you want to use a column number or a column letter to use in the inputbox that will ask for the last column to be included in the print range?
Also remember that you might have to change printer settings to accommodate width of print range.
Since it is only 5 rows, printing in landscape would probably the best.

 

[bh24524]

Is there any way to make it not require an input box? I'm basically just trying to make the process do as little clicking and typing as possible - just click a button and it will execute the printing of any rows that need printed. But it would be to column R. In the actual live sheet, I have the printer setting at fit to page. Are you meaning that this code would change that?

 

[jolivanes]

You can hard code it but then if it needs to be different you have to change it in the code.
No problem setting the range to Column R.

Sub Maybe_So()
Dim lr As Long, i As Long, j As Long, dataArr, arr
lr = Range("A:A").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
dataArr = Range("A5:D" & lr).Value
    For i = LBound(dataArr) To UBound(dataArr)
        If dataArr(i, 1) <> dataArr(i, 2) Then arr = arr & "," & dataArr(i, 4)
    Next i
   
    arr = Split(Mid(arr, 2), ",")
    Application.ScreenUpdating = False

    'You can delete the next 6 lines if not needed although they don't do any harm   
    With Worksheets("Sheet2").PageSetup    '<---- Change Sheet Name
        .Zoom = False
        .Orientation = xlLandscape
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
   
    For j = LBound(arr) To UBound(arr)
        With ActiveSheet.Range("A4:H" & lr)
            .AutoFilter 4, arr(j), xlFilterValues
            Range("A1").Resize(lr, 18).PrintOut     '<----- The number 18 is Column R
            .AutoFilter
        End With
    Next j
Application.ScreenUpdating = True
End Sub[/code

 

[bh24524]

Thanks Jolivanes, I tested it but it is actually only printing out the headers(the first 4 rows) It did print to column R which is great, it's just missing that one row containing the person's name and the rest of the info to Column R. We're close. Let me know.

 

[jolivanes]

It all works on a file created to mimic your picture in Post #1.

Did you change the sheet name to the name of the sheet with the data?
Are you running the macro when you have the sheet with the data as ActiveSheet, the visible sheet on your desktop?

What is the result when you run these snippets?

Sub AAAAA()
Dim lr As Long
lr = Range("A:A").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
MsgBox lr
End Sub

Are the names in Column D?

Do you get all the names from Column D when you run this?

Sub AAAAB()
Dim lr As Long, i As Long, j As Long, dataArr, arr
lr = Range("A:A").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
dataArr = Range("A5:D" & lr).Value
    For i = LBound(dataArr) To UBound(dataArr)
        If dataArr(i, 1) <> dataArr(i, 2) Then arr = arr & "," & dataArr(i, 4)
    Next i
    arr = Split(Mid(arr, 2), ",")
For j = Lbound(arr) To Ubound(arr)
MsgBox arr(j)
Next j
End Sub

 

[bh24524]

Sheet name was changed. I ran your first code - the shorter one, and I got a message pop up saying 233. I'm not sure what that one is. When I run the second code however, it is weird because it gives me a 4 separate pop-ups(I did a test for two people) and it is actually the 1. last name as one message, 2. the first name as another, 3. The last name of the next person, and 4. the first name of that person. Both were ones who would have changes, no one else was included. And yes names are in column D.

 

[bh24524]

It's weird, I see it works on the sample but not the live file. Do I need to update another portion of the code? I noticed here it has:
For j = LBound(arr) To UBound(arr)
With ActiveSheet.Range("A4:H" & lr)
.AutoFilter 4, arr(j), xlFilterValues
Range("A1").Resize(lr, 18).PrintOut '<----- The number 18 is Column R
.AutoFilter
End With
Should that H maybe also be changed? Maybe to R?

Or maybe something up here?

Dim lr As Long, i As Long, j As Long, dataArr, arr
lr = Range("A:A").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
dataArr = Range("A5:D" & lr).Value

 

[jolivanes]

So the names in your picture in Post #1 are not like the original? You have the names in Column D separated by a comma?

 

[bh24524]

Yes. Sorry, I did not know that that made a difference. When I made the sample I didn't know that name formatting would be a driving factor in the code.

 

[jolivanes]

Change this line

If dataArr(i, 1) <> dataArr(i, 2) Then arr = arr & "," & dataArr(i, 4)

to this

If dataArr(i, 1) <> dataArr(i, 2) Then arr = arr & "|" & dataArr(i, 4)

and this line

arr = Split(Mid(arr, 2), ",")

to this

arr = Split(Mid(arr, 2), "|")