Errors with printing multiple sheets with criteria (should be simple?)

I am attempting to write a macro that will automatically determine if certain sheets should print or not and then print several worksheets at once. I get an Block If without If error and a runtime error when attempting to test it. Can anyone advise?

Thanks so much!



Sub PrintSORPrecinct()
'
' PrintSORPrecinct Macro
'


Sheets("SOR - ALL Pg 1").Select
Sheets("SOR - ALL Pg 2").Select
Sheets("SOR - ALL Race 1-2").Select
Sheets("SOR - ALL Race 3-4").Select
Sheets("SOR - ALL Race 5-6").Select
If Sheets("SOR - ALL Race 7-8").Range("U7").Value = "Do Not Print" Then '
Sheets("SOR - ALL Race 7-8 ").Select False
If Sheets("SOR - ALL Race 9-10").Range("U7").Value = "Do Not Print" Then '
Sheets("SOR - ALL Race 9-10").Select False
If Sheets("SOR - ALL Race 11-12").Range("U7").Value = "Do Not Print" Then '
Sheets("SOR - ALL Race 11-12").Select False
If Sheets("SOR - ALL Race 13-14").Range("U7").Value = "Do Not Print" Then '
Sheets("SOR - ALL Race 13-14").Select False
Sheets("SOR - ALL Next to Last Pg").Select
Sheets("SOR - ALL Final Pg").Select

Application.ActivePrinter = "iR-ADV C350 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("SOR - ALL Pg 1").Select
End Sub

Do you want to run the check on every sheet in the workbook? See if this helps.

Sub printall()
Dim ws As Worksheet, wsarray() As Variant, counter As Long
For Each ws In Worksheets
    If ws.Range("U7").Value <> "Do not print" Then
        ReDim Preserve wsarray(0 To counter)
        wsarray(counter) = ws.Name
        counter = counter + 1
    End If
Next
Application.ActivePrinter = "iR-ADV C350 on Ne03:"
Sheets(wsarray).PrintOut Copies:=1
End Sub

In the original I had the word Then as the last word on those lines? Sorry no only the certain sheets need to be checked. It is a packet of various pages.

Is there a common factor that could identify all sheets to check?

For example, all of the sheets in your code begin with "SOR - ALL" could we use that, or are there sheets with similar names that should be excluded, or others that should be included?

Originally Posted by jason.b75

Is there a common factor that could identify all sheets to check?

For example, all of the sheets in your code begin with "SOR - ALL" could we use that, or are there sheets with similar names that should be excluded, or others that should be included?

Sorry on IPhone this evening! Yes we could use all of them that have "SOR All" and then identifying #. I don't mind to change the worksheets names either.

Sorry again. Only the race sheets need checked but we need to print page 1 and 2 and then the last two pages that do not need checked. Hope that makes sense!

I haven't done any testing but think this should work.

The first array holds a list of all sheets that should be printed without checking, the second array holds a list of sheets that should be checked before printing.

Sub printall()
Dim wsarray1 As Variant, wsarray2 As Variant, n As Long, ctr As Long
wsarray1 = Array("SOR - ALL Pg 1", "SOR - ALL Pg 2", "SOR - ALL Race 1-2", "SOR - ALL Race 3-4", "SOR - ALL Race 5-6", "SOR - ALL Next to Last Pg", "SOR - ALL Final Pg")
wsarray2 = Array("SOR - ALL Race 7-8", "SOR - ALL Race 9-10", "SOR - ALL Race 11-12", "SOR - ALL Race 13-14")

For n = 0 To 4
    If Worksheets(wsarray2(n)).Range("U7").Value <> "Do not print" Then
        ctr = ctr + 1
        ReDim Preserve wsarray1(0 To 6 + ctr)
        wsarray1(6 + ctr) = wsarray2(n)
    End If
Next
Application.ActivePrinter = "iR-ADV C350 on Ne03:"
Sheets(wsarray1).PrintOut Copies:=1
End Sub

I won't be able to test it til Monday morning. I am so very grateful. I am by far a newbie at this but could you explain the ctr command to me?

ctr is a variable (abbreviation of counter), not a command. It is simply used to keep a count of how many sheets from the second array are being added to the first array.

A bit of afterthought, if you needed to add or removed sheets from the list in either of the arrays then the code wouldn't allow for the changes. This version will.

Sub printall()
Dim wsarray1 As Variant, wsarray2 As Variant, n As Long, ub1 As Long
wsarray1 = Array("SOR - ALL Pg 1", "SOR - ALL Pg 2", "SOR - ALL Race 1-2", "SOR - ALL Race 3-4", "SOR - ALL Race 5-6", "SOR - ALL Next to Last Pg", "SOR - ALL Final Pg")
wsarray2 = Array("SOR - ALL Race 7-8", "SOR - ALL Race 9-10", "SOR - ALL Race 11-12", "SOR - ALL Race 13-14")

For n = 0 To UBound(wsarray2)
    If Worksheets(wsarray2(n)).Range("U7").Value <> "Do not print" Then
        ub1 = UBound(wsarray1) + 1
        ReDim Preserve wsarray1(0 To ub1)
        wsarray1(ub1) = wsarray2(n)
    End If
Next
Application.ActivePrinter = "iR-ADV C350 on Ne03:"
Sheets(wsarray1).PrintOut Copies:=1
End Sub

You will see that I have changed ctr to ub1, I could have reused ctr as it is effectively the same, but changed it so it's more meaningful to anyone reading the code. ub1 is an abbreviation of upper boundary of wsarray 1, which is a number that marks the position of the last record in the array.

I think I understand! I can't wait to try it. My son moves into his dorm this weekend so I didn't bring any work home with me. Thank you so very much!

Good Morning! I tried it and received an error 9. I clicked on Debug and the line beginning with "If Worksheets......" highlights in yellow. Any thoughts?

It sounds like the code is trying to print a sheet that doesn't exist.

Try it again and when you click Debug, hover your mouse over (n). You should see a number, which will refer to a position in the array, 0 being the first position.

Check the sheet name in that position in the array to make sure it matches the actual sheet name, including any underscores, spaces, periods, etc.

You are My HERO! I did have some unexpected spaces and it took a little time to figure out and then the Do Not Print had a space after on a couple of pages so I changed the formula to =1 and it worked like a charm. I can't wait to put this to use with some other items as well. I am beyond grateful! This will save so much time on my sets of forms for 25 locations.