Macro - Return to starting cell

[Guest]

I am writing a macro that will always start in a different cell, but needs to
reference back to the starting cell each time it is run. For example, if the
cursor is in cell d3 when the macro is run, the macro must return to d3 when
finished. The next time the macro is run, it will start in e3, and return to
e3 when finished. I use the macro recorder to write my macros, how do I
write it to have the routine always end in the starting cell?

 

[JE McGimpsey]

One way:

Dim rReturn As Range
Set rReturn = ActiveCell

'do your routine here

rReturn.Activate

 

[Guest]

OK I'm lost: How is this written

1) The cursor is in d3.
2) goto a3, copy a3:a7
3) *** how do i automate the return to d3? ***
4) paste to d3:d7

 

[JE McGimpsey]

To do it the way you say:

Dim rReturn As Range
Set rReturn = ActiveCell
Range("A3:A7").Select
Selection.Copy
rReturn.Activate
ActiveSheet.Paste

OTOH, you could replace the whole thing with

Range("A3:A7").Copy Destination:=ActiveCell

 

[Guest]

JE - Thanks for quick responses. How do I record the macro? Using macro
recorder is pretty limiting. It is easier said than done. Thanks - Sony

 

[R..VENKATARAMAN]

try something like this
(at ther beginning of the macro after public sub -----())

dim cc as range
set cc=activecell
then other code staements

(at the end of the macro before end sub
type

cc.select

try this and see whether you get what you want.

 

[Bob Phillips]

You don't.

Go into the VBIDE, Alt-F11, Insert a new code module, Insert>Module, and
add the macro

Sub CopyData()
Range("A3:A7").Copy Destination:=ActiveCell
End Sub

and run that from excel.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JE McGimpsey]

Take a look at David McRitchie's "Getting Started with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

JE - I got it. Thankyou very much for the insight on this one. I have some
final mods to make and good to go. Thanks again - Sony