VBA giving inexplicable error

[exceluser90]

I'm not entirely sure why, but all of a sudden this line of code:

Worksheets("Analysis").Activate

Where "Analysis" is the name of a worksheet is giving me Application-defined or object-defined error.

It's such a simple line of code and the code worked for the longest time until now, which is why I am so confused.

Any help would be greatly appreciated as my entire program will not work without this.

 

[jreedich]

Could you post the subroutine not just the line of code that is failing.

 

[exceluser90]

Private Sub Analysis_Click()
    Worksheets("Analysis").Activate
    Worksheets("Analysis").Cells.ClearContents
    Sheets("Main").Activate
    Dim r As Long, c As Long
    r = Range("B" & Rows.Count).End(xlUp).row
    c = Cells(18, Columns.Count).End(xlToLeft).Column
    If Not r = 1 And Not c = 1 Then
        Call save
        Sheets("Main").Range(Cells(17, 1), Cells(r, c)).Copy
        Worksheets("Analysis").Activate
        Worksheets("Analysis").Range("A18").Select
        Worksheets("Analysis").Paste
        With Application
            .CutCopyMode = False
        End With
        Sheets("Analysis").Activate
    End If
    
End Sub

 

[shg]

Is the worksheet hidden?

In any case, there's no need to select:

Private Sub Analysis_Click()
    Dim r As Long
    Dim c As Long
 
    Worksheets("Analysis").Cells.ClearContents
 
    With Worksheets("Main")
        r = .Cells(.Rows.Count, "B").End(xlUp).Row
        c = .Cells(18, .Columns.Count).End(xlToLeft).Column
        If r <> 1 And c <> 1 Then
            Call Save
            .Range(.Cells(17, 1), .Cells(r, c)).Copy Worksheets("Analysis").Range("A18")
        End If
    End With
End Sub

 

[exceluser90]

Now this gives me the same error:

    Worksheets("Analysis").Cells.ClearContents

It's so odd because the code worked for the longest time until now. I have no clue what changed.

 

[exceluser90]

I closed some other workbooks and now it works. I can only imagine that had some part in it.

Thanks nontheless.

 

[shg]

I had wondered if the workbook that contained "Analysis" might not be active, but then you'd have gotten a subscript error. If Analysis is in the workbook the code is in, you can use ThisWorkbook.Worksheets("Analysis").Cells.ClearContents