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?


--
Sony Luvy

One way:

Dim rReturn As Range
Set rReturn = ActiveCell

'do your routine here

rReturn.Activate



In article ,
"sony654" wrote:

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?

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

--
Sony Luvy


"JE McGimpsey" wrote:

One way:

Dim rReturn As Range
Set rReturn = ActiveCell

'do your routine here

rReturn.Activate



In article ,
"sony654" wrote:

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?

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


In article ,
"sony654" wrote:

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 - Thanks for quick responses. How do I record the macro? Using macro
recorder is pretty limiting. It is easier said than done. Thanks - Sony


--
Sony Luvy


"JE McGimpsey" wrote:

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


In article ,
"sony654" wrote:

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

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.

"sony654" wrote in message
...
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?


--
Sony Luvy

You don't.

Go into the VBIDE, Alt-F11, Insert a new code module, InsertModule, 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)

"sony654" wrote in message
...
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


--
Sony Luvy


"JE McGimpsey" wrote:

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


In article ,
"sony654" wrote:

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

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

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

In article ,
"sony654" wrote:

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

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
--
Sony Luvy


"JE McGimpsey" wrote:

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

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

In article ,
"sony654" wrote:

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