Macro to fire upon opening file

[David P.]

I have a shared workbook that I would like a macro to begin upon opening it
that will do the following:

Select sheet "Cathy"
Find cell with the value "This"
Hit home key (to place the cursor at the first column location)
Do the same for sheets named "Gus", "Ed H", "Chris", "Shutter", "Carpet".

Many thanks for your help.

 

[Dave Peterson]

Option Explicit
Sub Auto_Open()

Dim mySheetNames As Variant
Dim sCtr As Long
Dim FoundCell As Range
Dim wks As Worksheet

mySheetNames = Array("Cathy", "Gus", "Ed H", "Chris", "Shutter", "Carpet")

For sCtr = LBound(mySheetNames) To UBound(mySheetNames)
Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(mySheetNames(sCtr))
On Error GoTo 0

If wks Is Nothing Then
'do nothing
Else
With wks
Set FoundCell = .Cells.Find(what:="This", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
Application.Goto FoundCell.EntireRow.Cells(1), scroll:=True
End If
End With
End If
Next sCtr
End Sub

 

[David P.]

Dave. It worked like a charm as I expected. Your replies are always fantastic
and invaulable.

Can I ask you one more question for a completely different workbook? If I
wanted to do the exact same thing except with the following changes what
would the code be?:

Select sheet "Messages"
Find cell with the value "Today."
Hit home key (to place the cursor at the first column location)
(This time this is the only sheet I need this done for)

 

[Dave Peterson]

You can make just a couple of minor changes to the existing code:

mySheetNames = Array("Cathy", "Gus", "Ed H", "Chris", "Shutter", "Carpet") and this line
Set FoundCell = .Cells.Find(what:="This", _
become:

mySheetNames = Array("Messages") and this line
Set FoundCell = .Cells.Find(what:="today", _

Using an array is probably overkill--why loop when there's only one item?

'Cause it's the easiest change! <vbg>

Dave. It worked like a charm as I expected. Your replies are always fantastic
and invaulable.

Can I ask you one more question for a completely different workbook? If I
wanted to do the exact same thing except with the following changes what
would the code be?:

Select sheet "Messages"
Find cell with the value "Today."
Hit home key (to place the cursor at the first column location)
(This time this is the only sheet I need this done for)