Unable to select a cell in a worksheet

[DBrian7]

I need help troubleshooting some VBA code. The following lines of code are part of a larger procedure in an Excel workbook. Before running the procedure, the workbook is open and it contains four worksheets – “Chase8919,” “T_Export,” “D_Export,” and “BFCU&Chase.” The first step of the code clears the entire contents of worksheet “Chase8919,” then cell A1 is selected. The next two steps repeat this process for worksheets “T_Export” and “D_Export,” respectively. The fourth step goes to worksheet “BFCU&Chase” and selects the cell directly below the last non-empty cell in column A. Then the code goes to End Sub.

When I run the procedure there is no error message. The procedure successfully selects the cell directly below the last non-empty cell in column A of worksheet “BFCU&Chase.” It also successfully deletes the contents of worksheets “Chase8919,” “T_Export,” and “D_Export.” It also successfully selects cell A1 in worksheets “Chase8919” and “T_Export.” But it does not select cell A1 in worksheet “D_Export.” It’s as if it ignores the command, .Range("A1").Value = "" in Step 3. I have attempted to solve the problem by trying both the .Activate and .Select methods in steps 1, 2, and 3 instead of .Range("A1").Value = "" but those result in the error messages, “Run-time error ‘1004’: Activate method of Range class failed” and “Run-time error ‘1004’: Select method of Range class failed.”

Here are the lines code:

' Clear contents and select "A1" in worksheets Chase8919, T_Export, and D_Export, then go back to BFCU&Chase.
' Step 1: Delete all data in worksheet Chase8919
With ThisWorkbook.Worksheets("Chase8919")
.Cells.Clear
.Range("A1").Value = ""
End With

' Step 2: Delete all data in worksheet T_Export
With ThisWorkbook.Worksheets("T_Export")
.Cells.Clear
.Range("A1").Value = ""
End With

' Step 3: Delete all data in worksheet D_Export
With ThisWorkbook.Worksheets("D_Export")
.Cells.Clear
.Range("A1").Value = ""
End With

' Step 4: Select the cell directly below the last non-empty cell in column A of worksheet BFCU&Chase
With ThisWorkbook.Worksheets("BFCU&Chase")
Dim lastRowBFCU As Long
lastRowBFCU = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lastRowBFCU + 1, "A").Select
End With

 

[rlv01]

Are the worksheets protected?

 

[rlv01]

Ok, I think I see. In order for .Range("A1").Select to work, the worksheet must first be made the activesheet, e.g.:

    ' Step 3: Delete all data in worksheet D_Export
    With ThisWorkbook.Worksheets("D_Export")
        .Activate
        .Cells.Clear
        .Range("A1").Value = ""
        .Range("A1").Select
    End With
    
    ' Step 4: Select the cell directly below the last non-empty cell in column A of worksheet BFCU&Chase
    With ThisWorkbook.Worksheets("BFCU&Chase")
        .Activate
    Dim lastRowBFCU As Long
        lastRowBFCU = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(lastRowBFCU + 1, "A").Select
    End With

 

[rlv01]

Also, do you really need .Range("A1").Value = ""? You are already clearing all of the cells just before that line of code.

 

[DBrian7]

Ok, I think I see. In order for .Range("A1").Select to work, the worksheet must first be made the activesheet, e.g.:

    ' Step 3: Delete all data in worksheet D_Export
    With ThisWorkbook.Worksheets("D_Export")
        .Activate
        .Cells.Clear
        .Range("A1").Value = ""
        .Range("A1").Select
    End With
   
    ' Step 4: Select the cell directly below the last non-empty cell in column A of worksheet BFCU&Chase
    With ThisWorkbook.Worksheets("BFCU&Chase")
        .Activate
    Dim lastRowBFCU As Long
        lastRowBFCU = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(lastRowBFCU + 1, "A").Select
    End With

This worked perfectly. Thank you!

 

[MARK858]

Your other option is to use Application.Goto (No better just different)

    ' Step 3: Delete all data in worksheet D_Export
    With ThisWorkbook.Worksheets("D_Export")
        .Cells.Clear
        Application.Goto .Range("A1"), True
    End With
   
    ' Step 4: Select the cell directly below the last non-empty cell in column A of worksheet BFCU&Chase
    With ThisWorkbook.Worksheets("BFCU&Chase")
        Dim lastRowBFCU As Long
        lastRowBFCU = .Cells(.Rows.Count, "A").End(xlUp).Row
        Application.Goto .Cells(lastRowBFCU + 1, "A"), True
    End With

 

[DBrian7]

Ok, I think I see. In order for .Range("A1").Select to work, the worksheet must first be made the activesheet, e.g.:

    ' Step 3: Delete all data in worksheet D_Export
    With ThisWorkbook.Worksheets("D_Export")
        .Activate
        .Cells.Clear
        .Range("A1").Value = ""
        .Range("A1").Select
    End With
  
    ' Step 4: Select the cell directly below the last non-empty cell in column A of worksheet BFCU&Chase
    With ThisWorkbook.Worksheets("BFCU&Chase")
        .Activate
    Dim lastRowBFCU As Long
        lastRowBFCU = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Cells(lastRowBFCU + 1, "A").Select
    End With

Also, do you really need .Range("A1").Value = ""? You are already clearing all of the cells just before that line of code.

You’re right, I don’t need .Range("A1").Value = "". But please explain why the first two steps worked fine without the .Activate command. Cell A1 was selected in each of those worksheets.

 

[MARK858]

But please explain why the first two steps worked fine without the .Activate command. Cell A1 was selected in each of those worksheets.

Because they aren't using Select, you can only Select a cell on an Active sheet, the same as you can't select a cell manually without having the sheet active.

 

[rlv01]

Same answer as @MARK858

FWIW, there is a lot of commonality in steps 1-3. You could tighten up the code a bit there using a For loop.

    'Clear contents and select "A1" in worksheets Chase8919, T_Export, and D_Export,
    'then go back to BFCU&Chase.

    Dim WS As Worksheet
    Dim SheetToActivate As String
    With ThisWorkbook
        SheetToActivate = ActiveSheet.Name
        Application.ScreenUpdating = False
        For Each WS In .Worksheets
            Select Case WS.Name
                Case "Chase8919", "T_Export", "D_Export"
                    With WS
                        .Cells.Clear
                        .Activate
                        .Range("A1").Select
                    End With
                Case "BFCU&Chase"
                    With WS
                        .Activate
                        SheetToActivate = .Name     'to make sure sheet BFCU&Chase is active after everything is done
                        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Select
                    End With
            End Select
        Next WS
        .Worksheets(SheetToActivate).Activate
        Application.ScreenUpdating = True
    End With