I have files with loads of worksheets (about 100 each). When I enter data, it
is different for each sheet but I want to be in the same location on each
sheet. I start on my first sheet, locate the place I want to enter data and
enter the data into the cell. Now comes the problem - when I move to sheet 2,
I am in the active cell where I was last time I was in this sheet, not in the
same location as I have just left on sheet 1. I want to move from sheet to
sheet but stay in the same cell - can this be done? It was like this when I
used Lotus 123 but I can't seem to do it in Excel. Any help gratefully
received! BeanoKid

You need to use a macro to do this. Right click the XL icon next to the word
file in the upper left hand corner of the screen and select View Code. Paste
the following...

Private CURRENT_ADDRESS As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
Range(CURRENT_ADDRESS).Select
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
CURRENT_ADDRESS = ActiveCell.Address
End Sub
--
HTH...

Jim Thomlinson


"BeanoKid" wrote:

I have files with loads of worksheets (about 100 each). When I enter data, it
is different for each sheet but I want to be in the same location on each
sheet. I start on my first sheet, locate the place I want to enter data and
enter the data into the cell. Now comes the problem - when I move to sheet 2,
I am in the active cell where I was last time I was in this sheet, not in the
same location as I have just left on sheet 1. I want to move from sheet to
sheet but stay in the same cell - can this be done? It was like this when I
used Lotus 123 but I can't seem to do it in Excel. Any help gratefully
received! BeanoKid

Thanks - that seems to have done the trick - you're a star!

"Jim Thomlinson" wrote:

You need to use a macro to do this. Right click the XL icon next to the word
file in the upper left hand corner of the screen and select View Code. Paste
the following...

Private CURRENT_ADDRESS As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.ScreenUpdating = False
Range(CURRENT_ADDRESS).Select
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
CURRENT_ADDRESS = ActiveCell.Address
End Sub
--
HTH...

Jim Thomlinson


"BeanoKid" wrote:

I have files with loads of worksheets (about 100 each). When I enter data, it
is different for each sheet but I want to be in the same location on each
sheet. I start on my first sheet, locate the place I want to enter data and
enter the data into the cell. Now comes the problem - when I move to sheet 2,
I am in the active cell where I was last time I was in this sheet, not in the
same location as I have just left on sheet 1. I want to move from sheet to
sheet but stay in the same cell - can this be done? It was like this when I
used Lotus 123 but I can't seem to do it in Excel. Any help gratefully
received! BeanoKid

You can also try selecting all worksheets and then your cell. Unselect all
worksheets and use your cell. Go to the next sheet, the cell will be
selected.

Hope it helps!


"BeanoKid" wrote:

I have files with loads of worksheets (about 100 each). When I enter data, it
is different for each sheet but I want to be in the same location on each
sheet. I start on my first sheet, locate the place I want to enter data and
enter the data into the cell. Now comes the problem - when I move to sheet 2,
I am in the active cell where I was last time I was in this sheet, not in the
same location as I have just left on sheet 1. I want to move from sheet to
sheet but stay in the same cell - can this be done? It was like this when I
used Lotus 123 but I can't seem to do it in Excel. Any help gratefully
received! BeanoKid