VBA error: Activate method of Worksheet class failed

Question

Hi guys I have this bunch of code:

  For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Position calculation" Or ws.Name = "Strategies & weights" Then
    Else
        sheet_name = ws.Name
        Sheets(sheet_name).Visible = True
        ThisWorkbook.Worksheets(sheet_name).Activate
        ws.Range("A2").Select
        For Each c In Range("A2", "A1000")
            If c.Value = "" Then
                c.Activate
                searched_cell = ActiveCell.Offset(-1, 0).Address
                GoTo flag1
            End If
        Next c

Everytime when I try to run a code from a sheet called "Position calculation" I get the error saying

Run - time error '1004' Activate method of Worksheet class failed

I cannot distinguish why the code is running from other sheets, but I have to run this script exactly from the page causing me this sort of error.

Thank you in advance for your help

You do not need to use `Activate`. A must read for you :) [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Oct 13 '20 at 06:46

score 1 · Answer 1 · answered Oct 13 '20 at 08:09

I couldn't figure out why you receive the error that you complain about but it's certainly true that you wouldn't have the problem if you wouldn't ask for it (as has been pointed out to you by @Siddarth Rout in the comments above). In my analysis I found that your entire approach is a little cranked even if all Select and Activate statements are removed. Please consider the approach taken below.

Private Sub Try()

    Dim Ws      As Worksheet
    Dim NextRow As Long
    
    For Each Ws In ThisWorkbook.Worksheets
        With Ws
            If .Name <> "Position calculation" And .Name <> "Strategies & weights" Then
                NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                If NextRow <= 1000 Then Exit For
            End If
        End With
    Next Ws
    
    NextRow = WorksheetFunction.Max(NextRow, 2)
    MsgBox Ws.Name & vbCr & "cell " & Cells(NextRow, "A").Address(0, 0)
End Sub

This code will return the same result whichever sheet is active and regardless of whether a sheet is hidden or visible.

VBA error: Activate method of Worksheet class failed

1 Answers1