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

Question

So I'm trying to create a macro where all sheets will return to cell A1. In addition to this, if there are frozen cells on a sheet this macro would also scroll up and left on all sheets. The final piece of this code would return to the desired sheet.

As I step into this code I am getting this 1004 error at the beginning of the 3rd sheet. There are 8 sheets in this excel.

Sub Retun_to_A1()

'Retun_to_A1 Macro

 'returns selected cell to A1

 'Keyboard Shortcut: Ctrl+Shift+A

    For Each ws In ActiveWorkbook.Sheets
    ws.Select
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1
    Do Until Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Height > 0
    ActiveWindow.ScrollRow = ActiveWindow.ScrollRow + 1
    Loop
    Do Until Cells(ActiveWindow.ScrollColumn, ActiveWindow.ScrollColumn).Width > 0
    ActiveWindow.ScrollColumn = ActiveWindow.ScrollColumn + 1
    Loop
    Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Select
    Next ws
    For Each ws In ActiveWorkbook.Sheets
    ws.Select
    Range("A1").Select
    Next ws
    Sheets("JE Summary").Select
End Sub

updated


Sub Retun_to_A1()
'
' Retun_to_A1 Macro
' returns selected cell to A1
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If Not ws.ProtectContents And ws.EnableSelection = xlNoRestrictions And ws.Visible = xlSheetVisible Then
        Dim rng As Range
        Set rng = ws.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
        
        Application.Goto rng, True
    End If
Next

Sheets(5).Select

End Sub

BigBen BigBen · Accepted Answer · 2020-08-25T18:58:45

Instead of using Select here, one option is Application.GoTo. Also note that you actually need to be able to select the cell in question... maybe something like the following:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If Not ws.ProtectContents And ws.EnableSelection = xlNoRestrictions And ws.Visible = xlSheetVisible Then
        Dim rng As Range
        Set rng = ws.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
       
        Application.Goto rng, True
    End If
Next

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

1 Answers