Macro stopped working.

Hi,

I am using this macro to print selected sheets from a workbook with 229 sheets.
It should only print the sheets with qualifying data in two specific cells. It worked perfectly for about 2 weeks. I went home on Tuesday and everything was fine, came back on Thursday and I am getting a runtime 13 miss match error.

Any thoughts?

Sub macroprint()
Dim Sh As Worksheet
    Dim Arr() As String
    Dim N As Integer
    N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = xlSheetVisible And Sh.Range("E1").Value > "40544" And Sh.Range("C2").Value <> "Occupied" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
    Next
    If N = 0 Then
        MsgBox "No Vacant Dirty", vbExclamation
        Exit Sub
    End If
    With ActiveWorkbook
        .Worksheets(Arr).PrintOut
      
    End With
    
    End Sub

Thank you

Why did you go home in the first place .... ?

I'd try:

Sub macroprint()
  For Each Sh In ActiveWorkbook.sheets
    If Sh.Visible = -1 And Sh.Range("E1").Value > 40544 And Sh.Range("C2").Value <> "Occupied" Then c01 =c01 & "|" & sh.name
  Next

  If isempty(c01) Then
    MsgBox "No Vacant Dirty", vbExclamation
  else
    activeworkbook.sheets(split(mid(c01,2),"|")).printout
  End If
End Sub

I tried the new code. Thank you snb,

But I am still receiving the same error.

If you click Debug when the error message appears, which line of the code is highlighted?

I don't think my suggestion will produce that error.

Sh.Range("E1").Value > "40544"

is the culprit.

@snb

I don't think my suggestion will produce that error.

apparently it does. Otherwise the asker would not report it. Instead of tagging the component

Sh.Range("E1").Value > "40544"

as "the culprit", do you have a suggestion about how to troubleshoot the issue?

Sub macroprint()
  For Each Sh In ActiveWorkbook.sheets
    If Sh.Visible = -1 And val(Sh.Range("E1").Value) > 40544 And Sh.Range("C2").Value <> "Occupied" Then c01 =c01 & "|" & sh.name
  Next

  If isempty(c01) Then
    MsgBox "No Vacant Dirty", vbExclamation
  else
    activeworkbook.sheets(split(mid(c01,2),"|")).printout
  End If
End Sub

Has anything in the cells that are checked been changed? This might be on just one sheet

Have you used sheet protection to lock cells?

You might be able to protect the workbook structure using Workbook Protection.

Naturally, make sure you have a back up file at all times