Last available cell please turn out the lights.

[Lowell In the south]

I would like to thank... the acadmey, my coaches, all the other cast members ... my family, and MUDFACE, Andrew Poulsom, and ZZydhf for the expert wisdom they have given me recently.
Did i mention that i work late a LOT!!! and this stuff seems really funny when you are tired and all hoped up on mountain dew.

THE PROBLEM:
I need have a macro that is called from a work sheet change event. Well the macro copies ranges and then pastes them to other ranges. HOW do I make the selected cell return to the cell that caused the work sheet change even to take place? Cause it could be any cell on the worksheet. Thanks again!!!!

 

[kkknie]

I assume you are calling another subroutine that does the copying. If so, change the subroutine to accept a variable and pass the Target from the Worksheet_Change event like so:

Call CopyStuff(Target)

The CopyStuff routine should looke something like this:

Sub CopyStuff(inTarget)

At the end of the subroutine, add something like this:

Sheets("YourSheetNameHere").Select
Range(inTarget).Select

That should do it.

K

 

[klb]

Have your macro start off by giving your "cell" a range name such as "here". End the macro by telling it to go to "here". Or any name that you choose instead of "here"

 

[Lowell In the south]

Thanks for the suggestions!!

What code would i use to get the micro to name the last active cell and then select it after it had run?

 

[Derek]

Hi there
Looks like K's already answered that, but anyhow you can try something like this code - it goes to sheet2 and inserts data then returns to your original sheet and active cell.

ac = ActiveSheet.Name
Dim rng As Range
Set rng = Cells(ActiveCell.Row, ActiveCell.Column)
Sheets("Sheet2").Select
Range("A1").FormulaR1C1 = "Data entered"
Sheets(ac).Select
rng.Select
End Sub

regards
Derek
This message was edited by Derek on 2002-10-02 23:26