Multiple sheet printing based on cell value in each sheet

Craigos

New Member
Joined
Aug 6, 2010
Messages
34
Hi All, despite much searching and trials I almost have what I need but cannot work out the last bit.

I have 7 sheets as part of a training course session for each day of the week and two other sheets that have various stats on them drawn from the formula on the Monday to Friday sheets.

The following code works well:

Code:
[COLOR=black]Sub print_sessions()
Application.ScreenUpdating =False
Dim ws As Worksheet 
For Each ws In Worksheets 
Range("m1").Select        
If [M1].Value = "Yes" Then  
ws.Printout  
 End If
Next
Application.ScreenUpdating = True
End Sub[/COLOR]
It prints no problems if any sheet has a “Yes”, but what I need is to have a message box if there is a “No” in cell M1 on all sheets, in other words there has been no data input to anysheet. The “Yes” / “No” value works from a simple Countif formula on each sheet.

The message box would simply say “No data input to any sheet – Please enter data to print” and take me back to Sheet 1, A2

I have tried adding another loop to look for all “No”’ and inputting a message box in various places in the code but can’t work it out.

The ideal solution is 1: to loop through all the sheets and if there is a “Yes” on any sheet then print them but collate into one print job not individually and 2: if there is nothing to print (a “No” on every sheet) a message box showing as above.

Can anyoneassist?
UsingWin10 Excel 2016
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Try this

Code:
Sub print_sessions()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, vNo As Boolean
    
    vNo = False
    For Each ws In Worksheets
        ws.Select
        If [M1].Value = "Yes" Then
            ws.PrintOut
            vNo = True
        End If
    Next
    If vNo = False Then
        MsgBox "No data input to any sheet – Please enter data to print"
        Sheets(1).Select
        Range("A2").Select
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you DanteAmor, worked a dream.

Although initially I tested it with protection off and it worked and then it failed when protection was put back on, my bad as I didn't mention the protection.

I tweaked it for protection off, your code executed, protection on, printed and didn't print as per your solution.

Thanks again

Craigos
 
Upvote 0
Thank you DanteAmor, worked a dream.

Although initially I tested it with protection off and it worked and then it failed when protection was put back on, my bad as I didn't mention the protection.

I tweaked it for protection off, your code executed, protection on, printed and didn't print as per your solution.

Thanks again

Craigos

What protection do you have, book or sheet?
Did you modify the macro? you can put the complete code that you are using.
 
Upvote 0
Hi DanteAmor,

I will post what I did tomorrow when I am back in work as I don't have the access from home and appreciating the time difference.

I suspect my solution could well be improved and will take any advice once you see it.
Thanks, always great to have support.

Craigos
 
Upvote 0
Hi DanteAmor

What I have is below and my explanation is:

I have 7 sheets that are a: Visible (I have a validation sheet Very Hidden) and b. are protected less the cells that the user can input to.

Any of the 7 sheets could be printed depending upon data being input to any of those sheets and this is where your code works fine as long as the sheet protection on each is off.

With sheet protection on, the code runs and prints fine but then errors out with Run-time error ‘1004’, Method of object ‘_Worksheet’ failed. It highlights at the ws.Select point.

I then tried to incorporate my normal unprotect / protect code into your code but I believe it didn’t work. So, I moved the protection code outside of the print sub so it runs as:

Takes off the sheet Unprotect code first (all sheets) and calls the print code, print code runs then call the Protect code and l works fine, no errors and code use is as below.

Code:
Sub protection_off_print()
Application.ScreenUpdating = False
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MyPassword"
Next ws

Call print_sessions2

Application.ScreenUpdating = True
End Sub
[FONT=Times New Roman]
[/FONT]Sub print_sessions2()
   Application.ScreenUpdating = False
    Dim ws AsWorksheet, vNo As Boolean
      vNo = False
[FONT=Times New Roman]
[/FONT]    For Each ws InWorksheets
        ws.Select
        If [M1].Value= "Yes" Then
           ws.PrintOut
            vNo = True
        End If
    Next
    If vNo = FalseThen
        MsgBox"No data input to any sheet – Please enter data to print"
        Sheets(1).Select
        Range("A2").Select
    End If

Call protection_on_print

   Application.ScreenUpdating = True
End Sub
[FONT=Times New Roman]
[/FONT]Sub protection_on_print()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=" MyPassword "
Next ws
Sheet1.Select
Range("C2").Select
Application.ScreenUpdating = True
End Sub

Hope I have explained clearly the what happens and how I have worked around the error.

Very happy to see if it can be incorporated into the print code and if the printed sheets when they are run can be in one print job rather than each printing

Craigos.
Always Learning
 
Upvote 0
Try this

Code:
Sub print_sessions()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, vNo As Boolean
    
    vNo = False
    For Each ws In Worksheets
        
        If [COLOR=#ff0000]ws[/COLOR].range("M1").Value = "Yes" Then
            ws.PrintOut
            vNo = True
        End If
    Next
    If vNo = False Then
        MsgBox "No data input to any sheet – Please enter data to print"
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thats the one DanteAmor, must admit the more I looked and tried various permutations before I read your last solution the more I figured it didnt need my protracted solution.

Working fine now, thanks for your support

A definite :biggrin: from me.

Craigos
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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