Multiple sheet printing based on cell value in each sheet

[Craigos]

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:

[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

 

[DanteAmor]

Try this

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

 

[Craigos]

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

 

[DanteAmor]

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.

 

[Craigos]

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

 

[DanteAmor]

All right, I'll wait for news from you

 

[Craigos]

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.

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

 

[DanteAmor]

Try this

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

 

[Craigos]

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 from me.

Craigos

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.