Last available cell please turn out the lights.

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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!!!!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
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

Well-known Member
Joined
Apr 3, 2002
Messages
821
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

Board Regular
Joined
Sep 26, 2002
Messages
55
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,143,613
Messages
5,719,713
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top