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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,893
Office Version
2007
Platform
Windows
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
 

Craigos

New Member
Joined
Aug 6, 2010
Messages
34
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,893
Office Version
2007
Platform
Windows
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

New Member
Joined
Aug 6, 2010
Messages
34
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

Well-known Member
Joined
Dec 3, 2018
Messages
8,893
Office Version
2007
Platform
Windows
All right, I'll wait for news from you
 

Craigos

New Member
Joined
Aug 6, 2010
Messages
34
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,893
Office Version
2007
Platform
Windows
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
 

Craigos

New Member
Joined
Aug 6, 2010
Messages
34
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,893
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,082,316
Messages
5,364,491
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top