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...
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...
Share Improve this question edited Feb 17 at 20:45 braX 11.8k5 gold badges22 silver badges37 bronze badges asked Feb 17 at 20:45 PapaPapa 177 bronze badges 2- 1 Probably that your ranges aren't qualified with a worksheet. The real solution to this though is to not select anything in the code, you can do everything here without selecting a single thing. see here – Warcupine Commented Feb 17 at 20:49
- Do you want to run this function from a cell on a sheet? – Black cat Commented Feb 18 at 6:29
1 Answer
Reset to default 1To 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).