How to reset the active cell to the cell that the Macro starts on?

Question

I'm attempting to make a Macro that takes data from fixed cells in another sheet that I update and inputs the data into another sheet. Everyday I put new data into the source sheet and it will be transferred into the second sheet as that days data (one row). This requires the original cell I run the macro from to be the fixed point of reference. How do I reset the active cell the cell that I hit the Macro keystroke from?

I have tried Startcell = activecell or things of that type but they have not yielded results. I'm not good with VBA so it could have been a formatting error that resulted in this not working.

Sub Macro15()
'
' Macro15 Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Sheets("MACRO (insert data)").Select
    Range("G4:Q4").Select
    Selection.Copy
    Sheets("Jun-2019").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("MACRO (insert data)").Select
    Range("W4:AG5").Select
    Selection.Copy
    Sheets("Jun-2019").Select
    Range("C42").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O10:Y10").Select
    Selection.Copy
    Startcell.Offset(0, 11).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

If someone could help me just return the active cell to the start cell after "Range("O10:Y10").Select" and "Selection.Copy" that would be great.

First you need to capture the (initially) active cell. Do that with a variable. At the end of your code, you can then do <your_variable_name>.Select Related: Avoid Activate/Select wherever possible. None of your macro actually requires anything to be Selected, so this is an X/Y problem: you desire to return the initial selection, but that's only because you're altering the selection needlessly during runtime :) — David Zemens
Thanks for the help. I'm attempting now to remove all the activate and selections, but how would I store the initially active cell as a variable using Dim? — kev12222
Dim startRange as Range and then Set startRange = ActiveCell — David Zemens

David Zemens David Zemens · Accepted Answer · 2019-06-24T22:10:45

Something like this, untested. Note that after copyRange is first assigned, it's unclear where you need that data pasted/transferred. You'll need to update destSheet.Range(" ??? ") to refer to a proper address rather than " ??? ".

Note that we don't select or activate anything. We make use of the fact that Values from a range can be directly assigned to another range of equal size, simply by doing something like rng2.Value = rng1.Value. More information on copying values between workbooks/worksheets: here.

I've declared relevant variables which should make the code easier to maintain (e.g., there is now only ONE place where you need to change worksheet names in the future months, etc.)

Dim StartCell as Range
Dim copyRange as Range
Dim dataSheet as Worksheet   '## MACRO (insert data)
Dim destSheet as Worksheet   '## Jun-2019
Set dataSheet = Sheets("MACRO (insert data)")
Set destSheet = Sheets("Jun-2019")

Set StartCell = ActiveCell

'First, copy G4:Q4 values
Set copyRange = dataSheet.Range("G4:Q4")
' Update the ??? with the destination for this data from G4:Q4
destSheet.Range(" ??? ").Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value

'Second, copy W4:AG5 values:
Set copyRange = dataSheet.Range("W4:AG5")
destSheet.Range("C42").Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value

' ## Now set copy O10:Y10 from Jun-2019 worksheet
Set copyRange = destSheet.Range("O10:Y10")
' Finally put these values in the StartCell area
StartCell.Offset(0,11).Resize(copyRange.Rows.Count, copyRange.Columns.Count).Value = copyRange.Value
End With

How to reset the active cell to the cell that the Macro starts on?

1 Answers