Solved: Select a particular cell before closing

[volabos]

Dear all, I am using Excel2007 and would like to select the cell "A1" for all sheets before closing a particular file (saved in xlsm format). Therefore I wrote following code :

Private Sub Workbook_BeforeClose()
Dim ws As Worksheet
For Each ws In ActiveBook.Worksheets
Range("A1").Select
Next ws
End Sub


I placed above code in the "Module". However I notice that nothing is happening as, when I re-open that file, I do not see that "A1" cell is highlighted in any of the sheet :(

Would anyone please point me where I was wrong?

Best,

[Bob Phillips]

Put it in the workbook open event.

[mdmackillop]

For Each ws In ActiveBook.Worksheets
ws.Activate
Range("A1").Select
Next ws

'or

For Each ws In ActiveBook.Worksheets
Application.Goto ws.Range("A1")
Next ws

[volabos]

Thanks for those reply. According to those input, I wrote following codes in "Module"

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveBook.Worksheets
Range("A1").Select
Next ws
End Sub

Still nothing is happening here. I also tried with other codes but could not get them work. What to do next?

Thanks,

[Simon Lloyd]

The code needs to go in the ThisWorkbook code module here's how:
How to Save a Workbook Event Macro
1. Copy the macro above by selecting all the code, then Right Click selected code and Copy.
2. Press ALT+F11 keys to open the Visual Basic Editor.
3. Press CTRL+R keys to shift the focus to the Project Explorer Window
4. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
5. Press the Enter key to move the cursor to the Code Window
6. Paste the macro code using CTRL+V
7. Save the macro in your Workbook using CTRL+S

[volabos]

If I paste above code in ThisWorkbook code window, I am getting following error :
Run time error '424'
Object required
Please see the attached file ......

However if I put those code on the "module", there are no error however that code is not simply working.

[Bob Phillips]

Try this



Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Me.Worksheets
Range("B1").Select
Next ws
End Sub

[Simon Lloyd]

I've not looked at your workbook as i don't have xl2007 here but this will definitely work for you:
Private Sub Workbook_Open()
Dim i As Long
For i = 1 To Sheets.Count
With Sheets(i)
.Activate
.Range("A1").Activate
End With
Next i
End Sub

[volabos]

Ah, this is working...........thanks, However I made a small update :


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Activate
Range("A1").Select
Next ws
End Sub

[mdmackillop]

Hi Simon,
Your code will fail on Chart Sheets, you need specifically to refer to worksheets.

[Bob Phillips]

Ah, this is working...........thanks, However I made a small update :


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Activate
Range("A1").Select
Next ws
End Sub


Good spot!