Can't unselect all data rows

Question

This seems like a dumb question but I want to unselect all data rows in a sheet by selecting cell A1. If I manually select A1, all formerly selected cells are no longer selected; only A1 is selected like I want. By leaving the entire range selected, the user could unintentionally hit delete and delete all the data. Here's my function:

Function Copy_Prior_Sales(wsName_Target_Sheet As String, wsName_Source_Sheet As String, Targeted_Row As Integer) As Integer

    Dim lastRow As Integer
    Dim Start_Row As Integer, Target_Row As Integer
    If IsMissing(Targeted_Row) = True Or IsNumeric(Targeted_Row) = False Or Targeted_Row < 1 Then
        Target_Row = 1
    Else
        Target_Row = Targeted_Row
    End If
    If Target_Row > 1 Then
        Start_Row = 2
    Else
        Start_Row = 1
    End If

    Application.ScreenUpdating = False
    Sheets(wsName_Source_Sheet).Select
    lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("A" & Start_Row & ":I" & lastRow).Select
    Selection.Copy

    Sheets(wsName_Target_Sheet).Select
    Range("A" & Target_Row).Select
    ActiveSheet.Paste
    Range("A1").Select
    
    Range("A1:I" & lastRow).Select
    Range("A1:I" & lastRow).Columns.AutoFit
    Application.CutCopyMode = False
    Range("A1").Select

    Sheets(wsName_Source_Sheet).Select
    Range("A1").Select
    Application.CutCopyMode = False
    Copy_Prior_Sales = lastRow
    Application.ScreenUpdating = True
    
End Function

The sheet in question is wsName_Target_Sheet. After executing this code, all data rows are left selected even though I specifically selected RANGE("A1") a couple times.

I've even written a quick macro to loop through each sheet and select A1 but that doesn't work either.

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Range("A1").Select
Next ws
Application.ScreenUpdating = True

What am I not seeing? This seems so simple...

Answer 1

To ensure that only cell A1 is selected on the target sheet, you need to activate the worksheet before selecting a cell on it. In addition, for me, it's a good practice to minimize the use of Select and Activate because they can make your code less efficient and harder to read. Thus, you can adjust your function like this:

Function Copy_Prior_Sales(wsName_Target_Sheet As String, wsName_Source_Sheet As String, Optional Targeted_Row As Long = 1) As Long

    Dim lastRow As Long
    Dim Start_Row As Long
    Dim Target_Row As Long
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet

    ' Set the Target_Row
    Target_Row = IIf(Targeted_Row < 1, 1, Targeted_Row)
    Start_Row = IIf(Target_Row > 1, 2, 1)

    Set wsSource = ThisWorkbook.Sheets(wsName_Source_Sheet)
    Set wsTarget = ThisWorkbook.Sheets(wsName_Target_Sheet)

    Application.ScreenUpdating = False

    ' Find the last row in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Copy the data without selecting
    wsSource.Range("A" & Start_Row & ":I" & lastRow).Copy
    wsTarget.Range("A" & Target_Row).PasteSpecial xlPasteAll

    ' Autofit columns without selecting
    wsTarget.Columns("A:I").AutoFit

    ' Clear the clipboard
    Application.CutCopyMode = False

    ' Activate the target sheet and select A1
    wsTarget.Activate
    wsTarget.Range("A1").Select

    ' Ensure screen updating is re-enabled
    Application.ScreenUpdating = True

    ' Return the last row number
    Copy_Prior_Sales = lastRow

End Function

Explanation: First of all, By defining wsSource and wsTarget as worksheet objects, you reference the sheets directly, eliminating ambiguity. Secondly, When you need to select a cell, you explicitly activate the worksheet first with wsTarget.Activate. Finally, changed Targeted_Row and related variables to Long instead of Integer to accommodate all possible row numbers in Excel (since Integer only goes up to 32,767).