How to name the last used cell?

[Lowell In the south]

I know this is simple but I am taking those baby steps.

I am tring to make my macro return you to the last cell you were working in no matter where you are in the worksheet when it runs. I recived a suggestion to have the Macro name that cell prior to running and then select that cell after it runs. Will that work more than once? How do i code somting like that?

 

[Fryer Tuck]

Sounds like what you want is

Range("A1").SpecialCells(xlCellTypeLastCell).Select

That will put you at the last cell each time it is run (even if you change where the last cell is).

F.T.

 

[Lowell In the south]

It appear that bit of code takes me to the last cell in the last column and the last row of the spread sheet. Make sense? Thanks for your help!

 

[rikrak]

Hi Lowell,

This will name a cell or range of cells when its/their contents change.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Name = "lastCellChanged"
End Sub

You can select it/them with:
Range("lastCellChanged").select

 

[NateO]

Howdy Lowell, yes, this is a good way to do it, it'll overwrite. If you have you the following following code in the appropriate worksheet module:<pre>
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Name = "MyCell"
End Sub</pre>

Right-click the sheet tab, select view code and paste in the code.

Then in a normal module you had code like the following:<pre>
Sub GoBck()
On Error Resume Next 'for the halibut
Application.Goto "MyCell"
End Sub</pre>

Running GoBck will return you to the last edited cell if a cell on the correct worksheet. Or click f5 and select MyCell.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-02 18:05

 

[Lowell In the south]

Thanks for the suggestions!!!! Appears to be only one problem my macro is called by a change event. The macro copies and pastes a range in to several other ranges. I seems that this bit of code sees the last selected range as the "target" No matter where I put it In the worksheet change even or at the top of the macro. Make sense? Thanks much for your help!!!!!!!!!!

 

[rikrak]

What about this:

Sub test()

'save position current cell
currCell = ActiveCell.Address
currSheet = ActiveSheet.Index

' do your stuff
'copy
'paste
'......

'return to saved position
Sheets(currSheet).Activate
Range(currCell).Activate

End Sub

 

[Lowell In the south]

THanks!!! I used that bit of code before I called two diffrent macros that cut and pasted with in the same work sheet and it works great!!!!
ac = ActiveSheet.Name
Dim rng As Range
Set rng = Cells(ActiveCell.Row, ActiveCell.Column)

Dim C As Range
' *** Change sheet name to suit ***
For Each C In Sheets("Window Inputs").Range("AG6:AG63")
If UCase(C.Value) = "NO" Then
Range("AH:AH").EntireColumn.Hidden = False
Range("AJ:AO").EntireColumn.Hidden = False
Exit Sub
End If
Next C
'*** Call your other procedure here ***
Call Only_one_Panning_size_per_window
Range("AH:AH").EntireColumn.Hidden = True
Range("AJ:AO").EntireColumn.Hidden = True


' ====================== only one trim size per window====================


Dim D As Range
' *** Change sheet name to suit ***
For Each D In Sheets("Window Inputs").Range("AP6:AP63")
If UCase(D.Value) = "NO" Then
Range("AQ:AQ").EntireColumn.Hidden = False
Range("AS:AX").EntireColumn.Hidden = False
Exit Sub
End If
Next D
'*** Call your other procedure here ***
Call Only_one_trim_Size_per_window
Range("AQ:AQ").EntireColumn.Hidden = True
Range("AS:AX").EntireColumn.Hidden = True

Sheets(ac).Select
rng.Select

End Sub