Print Sheets Using Cell Values

[TellM1955]

I have a number of sheets which depending on the selection of ON in the grid shown would like to bring up the Print Dialog Box to print the sheet names in the hidden columns. Further if All is selected it prints all of the sheets listed. This grid is on a sheet called "Master" and the range used is J3:N10. Has anyone got a VBA solution to do this? Thank you.

 

[Micron]

I am in the middle of making 2 complicated cutting boards for Christmas so I may not be able to get back to this today. So to help me and others who might pitch in, I think some clarification is required. I'd loop rather than bothering with arrays since your range is so small.

So
- if K10 (for All Year) value is "ON" then print the sheets listed in M4 to M10
- if L10 (for Alpha) value is "ON" then print the sheets listed in N4 to N10
In either of the above cases it does not matter what cells might be "ON" above row 10.

- if K10 is not "ON" then loop over K4 to K9 and if a cell is "ON" then print the sheet name in .Offset(0,2) - in your example, M4 and M7 (Monday and Thursday Year).
- if L10 is not "ON" then loop over L4 to L10 and if a cell is "ON" then print the sheet name in .Offset(0,2) - in your example, only N8 (Friday-Alpha).

Is there a reason why you must open the print dialog instead of just printing the required sheets?

 

[TellM1955]

Micron, firstly thanks for getting back on this.
To answer your questions:
if All is selected print M4 to M9. it justs short cuts turning all the other sheets to ON.
Your correct in your description of question 2, to only print those sheets highlighted ON, from M4:M9 or N4:N9

The reason for the Print Dialog Box is the .prints will some times be needed to be sent as a PDF.

Thanks

 

[Micron]

The reason for the Print Dialog Box is the .prints will some times be needed to be sent as a PDF.

This is not decided when the dialog opens, correct? It is already decided, so give user the choice to print pdf and bypass the dialog? What is the other print method if not pdf? In other words, if I want to print the choice(s) as pdf, just do that and forget the dialog. If I don't want to print as pdf, then I don't know what other option you have in mind. Maybe you don't like the idea of eliminating the dialog for some reason but I'm not seeing it yet. One thing about the dialog is that you have to deal with the possibility that the user cancels it. Not too hard AFAIK, but I'm wondering why it needs to be shown at all, unless certain options are volatile, such as paper size or orientation.

if All is selected print M4 to M9

Except that there are 2 "All" possibilities, K and L? Or is there just one possibility?

 

[TellM1955]

Micron, again thanks for looking at this.
I will look to bypass the print dialog box in some way so the sheets either print to the printer or to a pdf.
The alternative to pdf was direct to the default printer.
There are 2 All possibilities either All Year sheets or All Alphabetical sheets.
Regards

 

[Micron]

I don't have your other sheets that need to be printed out. I also don't know where you want the printouts to go. So this code might get you started but likely will need modifying. When I look at it, I also can't help but think there might be a simpler approach. This code is on a sheet I named Master. Copy/paste into your sheet module. You can run it by clicking within the code and pressing F8 to step through, or just F5 and let it run. The message box lines are for me to see if it works as planned (the messages confirm which sheets should get printed). If you alter the cell values so that different ones are ON and re-test, the messages should change accordingly. To test for actual printouts, you'd comment out the message box lines (or remove them) and uncomment the ones I commented out.

Sub PrintSheets()
Dim i As Integer
Dim sht As Worksheet
Dim pdfPath As String
Dim bolSkipK As Boolean, bolSkipL As Boolean

pdfPath = ThisWorkbook.Path & "\"
If UCase(Range("K10")) = "ON" Then
    For i = 4 To 9
'        Set sht = Range("M" & i)
'        sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard
        MsgBox Range("M" & i)
    Next
    bolSkipK = True
End If

If UCase(Range("L10")) = "ON" Then
    For i = 4 To 9
'        Set sht = Range("N" & i)
'        sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard
        MsgBox Range("N" & i)
    Next
    bolSkipL = True
End If

If Not bolSkipK Then
    For i = 4 To 9
    If UCase(Range("K" & i)) = "ON" Then
'        Set sht = Range("K" & i).Offset(0, 2)
'        sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard
        MsgBox Range("K" & i).Offset(0, 2)
    End If
    Next
End If

If Not bolSkipL Then
    For i = 4 To 9
    If UCase(Range("L" & i)) = "ON" Then
'        Set sht = Range("L" & i).Offset(0, 2)
'        sht.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard
        MsgBox Range("L" & i).Offset(0, 2)
    End If
    Next
End If

Set sht = Nothing

End Sub

 

[TellM1955]

Micron, thank you for this. I've uploaded and pasted in the code but get an error when I remove the ' on the first commented section. I've uploaded the file as I'm not sure what the error is showing. Can you please advise?
Thanks

OneDrive

 

[Micron]

Set sht = WSMaster

Your sheet name is not WSMaster, plus the reference has to be enclosed in double quotes? I didn't have that in the code so why did you add it? Mine Set the variable in the loop to the name of the sheet contained in the list if it was supposed to be printed.
EDIT - just checked and found that your code won't compile so there are other issues you might want to look at also.

 

[TellM1955]

Micron, I'd run the code you'd sent and when the error appeared I attempted to identify what was generating the error code which is why the sheet WSMaster was changed.

Thanks for the heads up on the other area which needed investigating which I've now resolved. Thanks for your help I'll continue to work on it.

 

[Micron]

I didn't think the reference would have to be type cast to a string but apparently it does. So not
Set sht = Range("M" & i)
but
Set sht = Sheets(CStr(range("M" & i)))
That will get you past that point but raise another error. One thing at a time I guess.

In case you have not figured it out, Excel vba is not my strong point. Coming from Access vba and trying to learn a thing or two.