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.

 

[NateSC]

This was similar to something I have written before so I tweaked it to your case. You need to update the name of the worksheet (or change to the generic Activesheet), but here is the code that should work. I think you cannot print non-contiguous rows without it adding a page break so the macro copies the data to a "temp" sheet (it creates it, and later deletes it, if it doesn't already exist).

The biggest problem is if you could have multiple lines for a given employee. If so, this will print their page each time there is something in columns F-H.

Let me know if I misunderstood something.

Sub PrintRowsWithData()
Dim myFirstRow As Integer
Dim myLastRow As Integer
Dim myHeaderRow As Integer
Dim mySheet As Worksheet
Dim myDataRng As Range
Dim aRow As Range
Dim myRow As Integer
Dim myTest As String
Dim myData As Variant
Dim outSheet As Worksheet
Dim tfound As Boolean


    'Set worksheet name !UPDATE THIS FOR YOUR WORKSHEET!
    Set mySheet = Worksheets("Sheet11")
    
    ' Or this can be an alternate if you know you will be on the sheet you are processing
    'Set mySheet = ActiveSheet
    
    'Define Row pointers
    myHeaderRow = 4
    myFirstRow = 5
    myLastRow = mySheet.Cells(mySheet.Rows.Count, 4).End(xlUp).Row
    
    ' Check for a sheet called "temp".  If it doesn't exist, create it.
    ' This is needed to do the printing correctly later.
    For Each outSheet In ActiveWorkbook.Sheets
        If outSheet.Name = "temp" Then tfound = True
    Next
    
    If Not tfound Then
        ActiveWorkbook.Sheets.Add
        ActiveSheet.Name = "temp"
    End If
    
    Set outSheet = Worksheets("temp")
    mySheet.Select
        
    ' Reset any sort filters that may be in place
    If mySheet.AutoFilterMode Then mySheet.AutoFilter.ShowAllData
    
    myData = mySheet.Range("A" & myFirstRow & ":H" & myLastRow)
    Set myDataRng = mySheet.Range("A" & myHeaderRow & ":H" & myLastRow)
    
    ' Print setup for the output
    With outSheet.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
    End With

    ' Loop through, build the test phrase, filter the data, copy to outSheet, print outSheet
    For i = 1 To UBound(myData)
        myTest = myData(i, 6) & myData(i, 7) & myData(i, 8)
        If myTest <> "" Then
            myDataRng.AutoFilter field:=4, Criteria1:=myData(i, 4)
            myDataRng.SpecialCells(xlCellTypeVisible).Copy
            Paste outSheet.Range("A1")
            outSheet.PrintPreview
            outSheet.Range("A1:H" & outSheet.Cells(mySheet.Rows.Count, 4).End(xlUp).Row).Clear
            mySheet.AutoFilter.ShowAllData
        End If
    Next i
    
    ' A few steps to clean up things in the end
    If mySheet.AutoFilterMode Then
        ' Clear the filters
        mySheet.AutoFilter.ShowAllData
        ' Turn off the filters
        mySheet.AutoFilterMode = False
    End If
    
    ' If I created it, I delete it back out, but it prompts you to allow.
    ' I believe the prompt can be turned off if desired or you can get rid of this and leave the sheet.
    If Not tfound Then outSheet.Delete
    
End Sub

 

[bh24524]

This was similar to something I have written before so I tweaked it to your case. You need to update the name of the worksheet (or change to the generic Activesheet), but here is the code that should work. I think you cannot print non-contiguous rows without it adding a page break so the macro copies the data to a "temp" sheet (it creates it, and later deletes it, if it doesn't already exist).

The biggest problem is if you could have multiple lines for a given employee. If so, this will print their page each time there is something in columns F-H.

Let me know if I misunderstood something.

Sub PrintRowsWithData()
Dim myFirstRow As Integer
Dim myLastRow As Integer
Dim myHeaderRow As Integer
Dim mySheet As Worksheet
Dim myDataRng As Range
Dim aRow As Range
Dim myRow As Integer
Dim myTest As String
Dim myData As Variant
Dim outSheet As Worksheet
Dim tfound As Boolean


    'Set worksheet name !UPDATE THIS FOR YOUR WORKSHEET!
    Set mySheet = Worksheets("Sheet11")
   
    ' Or this can be an alternate if you know you will be on the sheet you are processing
    'Set mySheet = ActiveSheet
   
    'Define Row pointers
    myHeaderRow = 4
    myFirstRow = 5
    myLastRow = mySheet.Cells(mySheet.Rows.Count, 4).End(xlUp).Row
   
    ' Check for a sheet called "temp".  If it doesn't exist, create it.
    ' This is needed to do the printing correctly later.
    For Each outSheet In ActiveWorkbook.Sheets
        If outSheet.Name = "temp" Then tfound = True
    Next
   
    If Not tfound Then
        ActiveWorkbook.Sheets.Add
        ActiveSheet.Name = "temp"
    End If
   
    Set outSheet = Worksheets("temp")
    mySheet.Select
       
    ' Reset any sort filters that may be in place
    If mySheet.AutoFilterMode Then mySheet.AutoFilter.ShowAllData
   
    myData = mySheet.Range("A" & myFirstRow & ":H" & myLastRow)
    Set myDataRng = mySheet.Range("A" & myHeaderRow & ":H" & myLastRow)
   
    ' Print setup for the output
    With outSheet.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
    End With

    ' Loop through, build the test phrase, filter the data, copy to outSheet, print outSheet
    For i = 1 To UBound(myData)
        myTest = myData(i, 6) & myData(i, 7) & myData(i, 8)
        If myTest <> "" Then
            myDataRng.AutoFilter field:=4, Criteria1:=myData(i, 4)
            myDataRng.SpecialCells(xlCellTypeVisible).Copy
            Paste outSheet.Range("A1")
            outSheet.PrintPreview
            outSheet.Range("A1:H" & outSheet.Cells(mySheet.Rows.Count, 4).End(xlUp).Row).Clear
            mySheet.AutoFilter.ShowAllData
        End If
    Next i
   
    ' A few steps to clean up things in the end
    If mySheet.AutoFilterMode Then
        ' Clear the filters
        mySheet.AutoFilter.ShowAllData
        ' Turn off the filters
        mySheet.AutoFilterMode = False
    End If
   
    ' If I created it, I delete it back out, but it prompts you to allow.
    ' I believe the prompt can be turned off if desired or you can get rid of this and leave the sheet.
    If Not tfound Then outSheet.Delete
   
End Sub

Thanks Nate, I am getting a variable not defined error that highlights the name of the sub-procedure when I click debug. I have the following references enabled:

Is there one that I need to have checked that I don't?

Also, if I should need to add additional columns to include in the printing, what portion(s) of code should I edit to allow this?

 

[NateSC]

You must have Option Explicit enabled somewhere. When I did that (because I am a bad coder and usually don't have that specified), I found I didn't declare the "i". So just add this to the declarations or anywhere before the i loop:

Dim i as Integer

Also, please make a backup of our data before running the code. I don't think it will mess up anything, but it is better to be safe. Just copy the workbook or worksheet to another location and then give it a try.

 

[bh24524]

You must have Option Explicit enabled somewhere. When I did that (because I am a bad coder and usually don't have that specified), I found I didn't declare the "i". So just add this to the declarations or anywhere before the i loop:

Dim i as Integer

Also, please make a backup of our data before running the code. I don't think it will mess up anything, but it is better to be safe. Just copy the workbook or worksheet to another location and then give it a try.

Ah yep that's what it is. Next error I am getting is a compile error:

It then highlights the word Paste from the code line Paste outSheet.range("A1") Thoughts on that one? I did change the mySheet to equal Activesheet just as a heads up.

 

[jolivanes]

Sub Maybe()
Dim lr As Long, i As Long, j As Long, dataArr, arr
lr = Range("A:H").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
dataArr = Range("A5:H" & lr).Value
    For i = LBound(dataArr) To UBound(dataArr)
        If dataArr(i, 6) <> "" Then arr = arr & "," & dataArr(i, 4)
    Next i
    
    arr = Split(Mid(arr, 2), ",")
    Application.ScreenUpdating = False
    
    For j = LBound(arr) To UBound(arr)
        With ActiveSheet.Range("A4:H" & lr)
            .AutoFilter 4, arr(j), xlFilterValues
            .PrintOut
            .AutoFilter
        End With
    Next j
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

BTW, if you would answer my Post, don't quote. Just extra clutter that we don't need.
If you need to refer to a post, just use the post number.

 

[bh24524]

Thanks Jolivanes. This worked with my small sample example. I need to apologize because It just hit me that I am overcomplicating this. As I thought more on what I was asking, and I wish I would have thought of this at the beginning. If you look at columns A and B how they have some letters in there. I hadn't thought of this, but anytime column A and B are different from one another, there's always going to be some kind of data displayed in one of the columns from F and onward. That will always hold true no matter what. I thought maybe it would be better to use that in the coding instead somehow. Could we maybe simplify the code to basically follow the logic that if Column A value is different from B, print like it did? And if I would need to add additional columns of info to include in printing, what line of coding would I need to adjust? Oh and also, the first 3 rows I have as blanks but they would also be printed just FYI.

 

[jolivanes]

Re: "I thought maybe it would be better to use that in the coding instead somehow."
Can you explain why? Does that mean that there is a possibility that Column F would not have any data?
Is there never going to be more then a single name? i.e. The print range will always be A1 to ?5
How are you going to determine the last Column to be printed? InputBox that asks for Column Number?

 

[bh24524]

Sure thing. So it definitely is possible that column F might not have data, but possible that other columns after it DO have data - in which case the row should print. There would never be more than one person's name printed on each sheet - so really it would be like you were looking at a total of 5 rows printed every time, the headers that are in the first 4 rows, and then the persons name and data that is to be printed. (I only included one set of headers in this sample, but the prior 3 rows do need printed. I do think your initial code was actually doing that though.) As for last column to be printed, so in the sample above I made the max column H. In the actual live file it is column R. We had a change in process earlier this year where we had to eliminate columns as info was no longer needed. Currently column R is the max, but I just figured I'd give a smaller sample like above that way I could learn which portion of code I would need to alter should the need to add or reduce columns of info comes around again.

Whether columns have data or not is dependent on a lookup comparison that each columns formulas do between the codes in columns A and B. So just as info, everywhere I've typed sample data in column F thru G is actually comparison lookup formulas to a different tab. So looking at column F which in the above screenshot I designated as a shift column and just as an example not reflected above, Let's say John Smith 2 had a change and has different codes in columns A and B but let's pretend that they are for the same shift. Maybe he is going to work over in a different building but keep the same hours. Nothing would display in column F, but it might be possible that info now displays in a further column such as him being in the different building. Just the fact that some info is displaying somewhere warrants it needing to print.

I apologize if I gave too little or too much info, let me know if anything needs clarified and I will definitely do so.