Destination paste range is selected on original sheet after copy

Viewed 172

Hi I have a macro that copies Column H in Sheet1 into Column F in Sheet2, it works fine as intended, able to copy and paste all the values, however the range of Column F is selected in Sheet1 after running the macro and i don't know why or how to remedy it. Below is the macro i have, could any kind soul advice?

Sub Button1_Click()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
  Set wsCopy = Worksheets("Sheet1")
  Set wsDest = Worksheets("Sheet2")
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "H").End(xlUp).Row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "F").End(xlUp).Offset(1).Row
    wsDest.Range("F2:F" & lDestLastRow).ClearContents
    wsCopy.Range("H7:H" & lCopyLastRow).Copy wsDest.Range("F2")
End Sub
3 Answers

If you want your column F not to be selected you can just select another cell, let's say A1.
This would solve your problem of having the column selected (highlighted).

However, a there is a cleaner and faster way to Copy Paste which also doesn't select the Range after pasting it.

Just refer to your range and change its value using range1.Value = range2.Value, which will copy paste the values only. The idea is the same if you want to copy formats, of font size, etc. Just change the .Value with whatever property you want to copy/paste.
In your case, this would make:

Sub Button1_Click()
    Dim wsCopy As Worksheet, wsDest As Worksheet
        Dim lDestLastRow As Long, nbvalues As Long
        
        Set wsCopy = Worksheets("Sheet1")
        Set wsDest = Worksheets("Sheet2")
        
        lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "F").End(xlUp).Offset(1).Row
        
        nbvalues = wsCopy.Range(wsCopy.Range("H7"), wsCopy.Cells(wsCopy.Rows.Count, "H").End(xlUp)).Rows.Count
        wsDest.Range("F2:F" & lDestLastRow).ClearContents
        wsDest.Range("F2:F" & 2 + nbvalues).Value = wsCopy.Range("H7:H" & 7 + nbvalues).Value
End Sub

Note that for the last line, where VBA does the copy/paste, both ranges need to be the same lenght, which is why I use lCopyLastRow in both the source and the target range.

EDIT: In order to use .Value=.Value, both ranges need to be the same size. The nbvalues is a Long that represent the number of cells to paste in your other range, so "F2:F" & 2+ nbvalues is "Cell F2 to Cell F (2 + the number of cells to paste)"

.Value=.Value doesn't copy across formats. It will only copy across values. So if you want to copy across other stuff as well then this will not help. If you want to only copy values then go for .Value=.Value way.

Now to your question

and i don't know why or how to remedy it.

WHY: These is no documented reason why this happens. If you are copying within the sheet then you will not see this problem. This happens only when you are copying across sheets. In fact this Selection is not even a valid selection. If you type Debug.Print Selection.Address before the copy and after the copy code, you will notice that it doesn't give you the address of newly selected range.

Option Explicit

Sub Sample()
    Debug.Print Selection.Address
    Sheet1.Range("E6:E15").Copy Destination:=Sheet2.Range("M6:M15")
    Debug.Print Selection.Address
End Sub

HOW: You can get rid of it by turning off ScreenUpdating.

Here is an example

Sub Sample()
    Application.ScreenUpdating = False
    
    '~~> Sample Copy code
    Sheet1.Range("E6:E15").Copy Destination:=Sheet2.Range("M6:M15")
    
    Application.ScreenUpdating = True
End Sub

Or a better way to write the same code as

Option Explicit

Sub Sample()
    Dim currentScreenUpdating As Boolean
    
    On Error GoTo Whoa
    
    '~~> Store Users current ScreenUpdating state
    currentScreenUpdating = Application.ScreenUpdating
    
    '~~> Turn off screen updating
    Application.ScreenUpdating = False
    
    '~~> Sample Copy code
    Sheet1.Range("E6:E15").Copy Destination:=Sheet2.Range("M6:M15")
    
LetsContinue:
    '~~> Restore Users ScreenUpdating state
    Application.ScreenUpdating = currentScreenUpdating
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

In Action

enter image description here

If you switch between worksheets, the selected area disappears.

Try adding:

wsDest.Activate
wsCopy.Activate

as the last two lines in the Sub - it clears the selection.

Related