Quick Question: How to come back to original cell location?

[John]

Almost done with my macro!

Initially, I place my curse in a location. And after a few
moves around the macro (Hard coded and Offsets) I want to
return back to the original location where I initially
placed my curser.

Any help is apprectiated!!

 

[Frank Kabel]

Hi
normally no need for selections within your macro (you may post your
code for 'correction'). But if you want to store the original active
cell try:

sub foo()
dim wks as worksheet
dim rng as range
set wks = activesheet
set rng=wks.activecell
'your code
wks.activate
rng.select
end sub

 

[JE McGimpsey]

The easiest way is to save the initial cell in a range variable:


Dim rOldActiveCell As Range
Set rOldActiveCell = ActiveCell

'your code here

Application.GoTo rOldActiveCell


However, the *best* way to do this is to never "move" the active cell.
Instead of using Select/Activate, use the Range objects directly. For
example, instead of


ActiveCell.Offset(1, 0).Select
ActiveCell.Copy
ActiveCell.Offset(0, 5).Select
ActiveCell.PasteSpecial Paste:=xlValues


use:

ActiveCell.Offset(1, 5).Value = ActiveCell.Offset(1, 0).Value

or, slightly more efficiently:

With ActiveCell
.Offset(1, 5).Value = .Offset(1, 0).Value
End With

 

[Guest]

Awesome! Thanks!

-----Original Message-----
The easiest way is to save the initial cell in a range variable:


Dim rOldActiveCell As Range
Set rOldActiveCell = ActiveCell

'your code here

Application.GoTo rOldActiveCell


However, the *best* way to do this is to never "move" the active cell.
Instead of using Select/Activate, use the Range objects directly. For
example, instead of


ActiveCell.Offset(1, 0).Select
ActiveCell.Copy
ActiveCell.Offset(0, 5).Select
ActiveCell.PasteSpecial Paste:=xlValues


use:

ActiveCell.Offset(1, 5).Value = ActiveCell.Offset(1, 0).Value

or, slightly more efficiently:

With ActiveCell
.Offset(1, 5).Value = .Offset(1, 0).Value
End With






.

 

[Dave Peterson]

dim CurCell as range
set curCell = Activecell
''''
application.goto curcell ', scroll:=true

But it's pretty unusual to actually have to select other cells. Maybe you could
just work on them directly:

mycell.offset(1,3).value = "abcde"

Instead of selecting first.