Excel VBA current range cell selection issue

Question

i have this code below in which i select range and then to offset to get the handle of one cell down of base_red range. The moment the next line of pastespecial is run it copies the data to range "mon" but also the cursor selection moves to "mon" range.

I don't want the selection to remain at the old place only and not able to figure out how to stop the selection at old place only. I require this because i have to do some more offset and paste the values to new locations.

If count1 = 1 Then
        ws.Range("base_red").Select
    End If
    ActiveCell.Offset(1, 0).Select 
    ActiveCell.Copy

    ws.Range("mon").PasteSpecial

A bit confused as to your question, but normally best practice is to [avoid using `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Oct 09 '20 at 17:49
you can potentially do it in one line `ws.Range("mon").value = ws.Range("base_red").Offset(1, 0).value` — izzymo, Oct 09 '20 at 18:09

score 0 · Answer 1 · answered Oct 09 '20 at 18:25

You may not want to use select. The following code copies one range to another. I implied from your example that you want to ensure you only copy and paste a single cell. If that is not the case, remove both instances of ".Cells(1,1)" from the code.

Public Sub Temp()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    count1 = 1
    If count1 = 1 Then
        ws.Range("mon").Cells(1, 1) = ws.Range("base_red").Offset(1, 0).Cells(1, 1)
    End If
End Sub

Here is another method which makes the fewest changes to your code.

Public Sub Temp()
    Dim rng As Range
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    count1 = 1
    If count1 = 1 Then
        ws.Range("base_red").Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Copy

    ' save the location of the active cell to rng.
    Set rng = ActiveCell
    
    ws.Range("mon").PasteSpecial
    ' return to the previous active cell
    rng.Select

End Sub

Excel VBA current range cell selection issue

1 Answers1