Move to same cell, next worksheet

[vicki.wells]

I have multiple worksheets with identical formatting to update with
unique data. Is there a way to move from the current curser location
to the same location in a different worksheet (same workbook).
Grouping the sheets puts the same data in all the sheets. I need same
location, different data.

 

[Dave Peterson]

I don't understand why you'd want to do this kind of thing, but...

You could group the sheets, select a cell, and ungroup the sheets.

If you want the selection to be the same when you swap between sheets, you could
use a macro.

This kind of thing would drive me nuts, but if you want...

This goes in the ThisWorkbook module.

Option Explicit
Dim mySelAddr As String
Dim mySheetNames As Variant
Private Sub Workbook_Open()

If IsArray(mySheetNames) Then
'do nothing
Else
Call MakeListOfSheets
End If

End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim res As Variant

If IsArray(mySheetNames) Then
'do nothing
Else
Call MakeListOfSheets
End If

res = Application.Match(Sh.Name, mySheetNames, 0)
If IsError(res) Then
'not on the list
Exit Sub
End If

If mySelAddr = "" Then
'do nothing
Else
Application.EnableEvents = False
Sh.Range(mySelAddr).Select
Application.EnableEvents = True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)

Dim res As Variant

If IsArray(mySheetNames) Then
'do nothing
Else
Call MakeListOfSheets
End If

'check to see if it's one of the sheets
'you care about
res = Application.Match(Sh.Name, mySheetNames, 0)
If IsError(res) Then
'not on the list
Exit Sub
End If

mySelAddr = Target.Address(0, 0)

End Sub
Private Sub MakeListOfSheets()
mySheetNames = Array("sheet1", "sheet2", "sheet4")
End Sub

===========

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Don Guillett]

Put in the ThisWorkbook module. Or set up a looping macro with the change to
do it automatically.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
si = ActiveSheet.Index
ta = Target.Address
Application.Goto Sheets(si + 1).Range(ta)
End Sub

 

[vicki.wells]

Put in the ThisWorkbook module. Or set up a looping macro with the changeto
do it automatically.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
si = ActiveSheet.Index
ta = Target.Address
Application.Goto Sheets(si + 1).Range(ta)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software





- Show quoted text -

Thank you for the suggestion, but how would the looping macro look?

 

[Don Guillett]

You would need to set your parameters for each sheet something like
sub dosheets()
for each ws in worksheets
select case ws.name
"joe":x=2
"bill":x=3
case else
end select
ws.range("a1")=x
next ws
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Put in the ThisWorkbook module. Or set up a looping macro with the change
to
do it automatically.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)
If Target.Address <> Range("a1").Address Then Exit Sub
si = ActiveSheet.Index
ta = Target.Address
Application.Goto Sheets(si + 1).Range(ta)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message





- Show quoted text -

Thank you for the suggestion, but how would the looping macro look?