select A1 on each sheet and activate sheet 1 when opening excel file

[k_amy21]

Hello,

I would appreciate very much some help on setting up a VBA code that will simultaneously do the following:
- activate sheet 1
- select cell A1 in each of the sheets when they are activated (i.e. when I click on them)
when I open my excel file that contains several sheets.

Since I am not so familiar with programming, I googled a bit and found the following solutions for EACH of my requests:
1. How to activate sheet 1:
Private Sub Workbook_Open()
Sheet1.Range("A1").Select
End Sub
2. How to select A1 in each of the sheets when they are activated:
For Each WSheet In Worksheets
WSheet.Activate
Range("A1").Select
Next
End Sub

My concern is now how to combine these two codes into one.

Looking forward to receiving an efficient solution.


Brgs
Amy

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

And

Please Login or Register  to view this content.

I may be wrong but I think the second code is default anyway.

[k_amy21]

Thanks for the quick answer. Unfortunately, the code 2 does not work. It doesn't activate cell A1.

Any other suggestions?

Please keep in mind that I would like ONE code that will perform both actions. I already tried with the codes that I mentioned in my 1st post, one below the other. But as soon as I add the 2nd one, the 1st one is "excluded" and won't perform it's "duty" any more.

[JOHN H. DAVIS]

Both should work. They go in the Thisworkbook module. Seems you already know this from your first post. In that module is a Sheet Activate Event on the right hand side drop down. Place the 2nd code there. I tested it before providing it.

[k_amy21]

I attachd a screen shot of how I did it. Please have a look... (I placed the code in the yellow-highlighted "ThisWorkbook").
It still won't work.

[JOHN H. DAVIS]

It's in the right place. It works for me. Does this work for you??

[k_amy21]

me again.

I got an answer! This is the code that worked good for my file:
Private Sub workbook_Open()

Dim x As Long

Application.ScreenUpdating = False

For x = Worksheets.Count To 1 Step -1
With Worksheets(x)
If .Visible = True Then
Application.Goto .Range("A1"), True
End If
End With
Next x

Sheets(1).Select

Application.ScreenUpdating = True

End Sub

I am still wondering why none of the other codes worked for me, while for the others did. Does my excell have a bug or just an "unexperienced user"???!

I appreciate all of your effort!!! Thanks a lot.

[k_amy21]

No, it doesn't work. Weird. (??) Here is what is shows...

[bakerman2]

This should definitly work

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Did you download the attachment provided? Did you test it?

[k_amy21]

@ JOHN H. DAVIS: yes, I tested it. But it didn't work. I proivded a screen shot on my last post (@ 1:53 PM).

@ bakerman2: Your code works, as well! Thnx for showing another apprach.