Batch Print

csmithbuffalo

New Member
Joined
Oct 7, 2024
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello- I use Excel as a prep tool for our company payroll. I have a tab for each employee. I use it for overtime calculations. On each tab, i have a section where i calculate the number of hours per week then a few columns over (but still on same tab) i have a section that calculates the actual overtime pay (we use a weighted average). I have to print this off for anyone that has overtime. I usually have around 30-40 tabs I will have to print for, but the total number of tabs i have is around 50- so not all employees have overtime. I would like to do a batch print. But i have 2 sequences in order to get what I need. I have it set to only print the section I need in each tab (green shaded area), but i also would like to know if I can narrow down to only print those tabs that have data in a particular cell (orange shaded area). I have attached pictures.

In the green area, that is my "print area" but I would like to narrow it down to only print those that have a number in the orange shaded cell. If the orange cell is 0.00, that means that employee did not have overtime, therefore do not need to print anything. Hope that makes sense.

Thanks!
 

Attachments

  • Employee 1.PNG
    Employee 1.PNG
    67.3 KB · Views: 7
  • Employee 2.PNG
    Employee 2.PNG
    64.9 KB · Views: 7

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
In which Cell (Column) is "Total OT Prem"?
Looks like it could be in Column Q
 
Upvote 0
It is column S, and I believe it's row 24. I'm not at work right now so I can't double check. But from my image I attached, I think it is 24
 
Upvote 0
No, it is not. The value is in Column S.
The string is either 1 or 2 columns to the left of it.
The row that it is in shows different in both pictures.
 
Upvote 0
No, it is not. The value is in Column S.
The string is either 1 or 2 columns to the left of it.
The row that it is in shows different in both pictures.
Oh whoops yes I was thinking you meant how much OT amount is. "Total OT prem" is in the column to the left. But the cell that I need to trigger an action would be in column S. If that cell is greater than Zero - on all tabs - I need it to print the set area I have defined for each tab
 
Upvote 0
Oh whoops yes I was thinking you meant how much OT amount is. "Total OT prem" is in the column to the left. But the cell that I need to trigger an action would be in column S. If that cell is greater than Zero - on all tabs - I need it to print the set area I have defined for each tab
And I can tweak the row to make it the same row for each tab. Just mainly trying to see if what I asked is possible, and if so, how do I do it
 
Upvote 0
And I can tweak the row to make it the same row for each tab. Just mainly trying to see if what I asked is possible, and if so, how do I do it
I don't know how to edit my post, but I did re read the post and it is confusing. The orange shaded area also includes the words, but I just need it to be triggered by the dollar amount in column S, not the words "total OT prem"
 
Upvote 0
This assumes that the amount and the words "Total OT Prem" can be in different rows in different sheets
It also assumes that the wording "Total OT Prem" is in Column 18 (Column R)

Code:
Sub Maybe()
Dim sht As Worksheet
Dim foundRow As Range
    For Each sht In ThisWorkbook.Worksheets
    Set foundRow = sht.Columns(18).Find("Total OT Prem", , , 1)
    If Not foundRow Is Nothing Then
        If foundRow.Offset(, 1).Value > 0 Then sht.Range("K1:S" & foundRow.Offset(1).Row).PrintOut
    End If
    Next sht
End Sub
 
Upvote 0
Solution
And I can tweak the row to make it the same row for each tab. Just mainly trying to see if what I asked is possible, and if so, how do I do it

This assumes that the amount and the words "Total OT Prem" can be in different rows in different sheets
It also assumes that the wording "Total OT Prem" is in Column 18 (Column R)

Code:
Sub Maybe()
Dim sht As Worksheet
Dim foundRow As Range
    For Each sht In ThisWorkbook.Worksheets
    Set foundRow = sht.Columns(18).Find("Total OT Prem", , , 1)
    If Not foundRow Is Nothing Then
        If foundRow.Offset(, 1).Value > 0 Then sht.Range("K1:S" & foundRow.Offset(1).Row).PrintOut
    End If
    Next sht
End Sub
Thank you- I will check this out and see if it works!

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,224,223
Messages
6,177,256
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top