PDA

View Full Version : [SOLVED] Range all sheets to cell "A1" before closing file



brunces
05-05-2005, 10:14 AM
Friends,

Before closing the file, I want Excel to automatically organize sheets, putting the cursor in cell "A1" of each of them and activating the first sheet, then I want it to save the workbook.

To do this, I use the code below...



Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Sheet5").Select
Range("A1").Select

Sheets("Sheet4").Select
Range("A1").Select

Sheets("Sheet3").Select
Range("A1").Select

Sheets("Sheet2").Select
Range("A1").Select

Sheets("Sheet1").Select
Range("A1").Select

ThisWorkbook.Save

End Sub


Is there any other "easier" way to do this?

Thanks for your attention, guys.

Hugs. :)

Bruno

Bob Phillips
05-05-2005, 10:21 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sh
For Each sh In ActiveWorkbook.Sheets
sh.Select
Range("A1").Select
Next sh
ThisWorkbook.Save
End Sub

Zack Barresse
05-05-2005, 10:23 AM
We do have a Kbase entry for that: http://www.vbaexpress.com/kb/getarticle.php?kb_id=328 ;)

But xld's code is customized for you. :yes

brunces
05-05-2005, 10:34 AM
xld, thank you very much. That was exactly what I needed.

firefytr, thanks for the link. Very good.

And thanks to both for the quick replies. :)

Hugs.

Bruno