VBA works in 1 workbook but not another

[Magestick]

Evening, All!

I've come across a problem that I'm unable to find an answer to. Any help would be greatly appreciated. Please keep in mind that I don't consider myself an expert when it comes to VBA. I have learned a lot in the last couple years, but I'm definitely still learning. Anyway...my problem is as follows:

I have 5 versions of a workbook due to the size of the file. The largest one is 12.5MB and the smallest one is just over 1MB. I've written the code so that it can be used in any of the 5 workbooks, but today, I ran into an issue when running a procedure. Excel is crashing saying that "Method 'Activate' of Object '_Worksheet' Failed". I've been using this code for quite some time, so I don't understand why I'm seeing problems with it now. Here's my code.

Sub Set_Print_Area()
    Dim LR As String
    Dim i As Long
    
    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = "Start" Then
            Exists = True
        End If
    Next i
    
    If Exists Then
        For i = 3 To 12
            If Sheets(i).Visible = True Then
                Sheets(i).Activate
                If Range("C10") <> "" Then
                ActiveSheet.Unprotect Password:="splicesheet"
                LR = (Range("C10").Value + 9)
                    ActiveSheet.PageSetup.PrintArea = Range((Cells(1, 2)), (Cells(LR, 20))).Address
                ActiveSheet.Protect Password:="splicesheet", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                    , AllowFormattingCells:=True
                Range("H10").Select
                End If
            End If
        Next i
        Else
            For i = 2 To 11
                If Sheets(i).Visible = True Then
                    Sheets(i).Activate
                    If Range("C10") <> "" Then
                    ActiveSheet.Unprotect Password:="splicesheet"
                    LR = (Range("C10").Value + 9)
                        ActiveSheet.PageSetup.PrintArea = Range((Cells(1, 2)), (Cells(LR, 20))).Address
                    ActiveSheet.Protect Password:="splicesheet", DrawingObjects:=True, Contents:=True, Scenarios:=True _
                        , AllowFormattingCells:=True
                    Range("H10").Select
                    End If
                End If
            Next i
    End If
End Sub

Thank you in advance for any help. I'm sure my code isn't as efficient as it could be, so any hints would be appreciated as well.

~Daron

 

[alansidman]

When you get the error message and click on debug, which line of code is highlighted. This is important to debugging your code.

 

[Magestick]

I wish it was that simple. I would definitely be able to figure it out then. Unfortunately, I'm not getting an error message. Excel crashes and does that thing where it acts like it's trying to save my work. I will add, however that if I add msgbox "done" after each subroutine callout, it makes it all the way through the code. The problem with this is that the user will have to hit "OK" 5 times each time they use the OneStepUpdate procedure...which is frequently. I tried adding DoEvents, but that didn't fix the issue.

Thank you for taking the time to ask. Just wish it was that simple.

~Daron

 

[Magestick]

So...When I use these templates, I usually have another file similar to it open. I just closed the other file and ran the code exactly as it's posted above and it went through. I'm wondering if I have some sort of conflict with an earlier version of this workbook that's causing problems only when I have that version open at the same time. Should I maybe make sure I'm calling subroutines with the workbook name that contains them?

~Daron