Workbook Close Command

[Jan Nademlejnsky]

I need to "clean up" each sheet in the spreadsheet before it is saved. I am
using the routine which was suggested on this site. I am getting error when
trying to unhide rows and columns. The rows and columns might not be hidden
in the first place. Just in case that someone hid any row or columns I want
to unhide them for the next user. I need to fix the syntax, please:

Dim wks As Worksheet
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Range("B2").Select
.AutoFilterMode = False
Application.Goto reference:=.Range("A1"), Scroll:=True
.Range("a3").Select
.Protect
End With
Next wks
Sheets("sheet1").Select 'Goes to first sheet
Range("a3").Select
End Sub


Thanks

Jan

 

[Dave Peterson]

You can only select stuff that's on the active worksheet. And when you loop
through the worksheets, you didn't select the worksheet first.

But you can probably do what you want without selecting anything:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect
With .Cells
.EntireRow.Hidden = False
.EntireColumn.Hidden = False
End With
.AutoFilterMode = False
Application.Goto reference:=.Range("A1"), Scroll:=True
.Range("a3").Select
.Protect
End With
Next wks
Sheets("sheet1").Select 'Goes to first sheet
Range("a3").Select
End Sub

If you still wanted to select the ranges (which slows things down), you could
add a line to select the sheet first:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet

For Each wks In Me.Worksheets
With wks
.Select
.Unprotect
Cells.Select
Selection.EntireRow.Hidden = False
Selection.EntireColumn.Hidden = False
Range("B2").Select
.AutoFilterMode = False
Application.Goto reference:=.Range("A1"), Scroll:=True
.Range("a3").Select
.Protect
End With
Next wks
Sheets("sheet1").Select 'Goes to first sheet
Range("a3").Select

End Sub

And I did select .range("a3"), but the application.goto actually selected that
sheet first.

 

[Jan Nademlejnsky]

Thank you, Dave, very much. It works as I wanted.

Jan