Option Explicit
Sub TidyAndReturn()
Dim cel As Range, sh As Worksheet, SRw As Long, SCol As Long, i As Long
Application.ScreenUpdating = False
Set sh = ActiveSheet
Set cel = ActiveCell
SRw = ActiveWindow.ScrollRow
SCol = ActiveWindow.ScrollColumn
For i = Sheets.Count To 1 Step -1
If Not IsChart(Sheets(i).Name) Then
Sheets(i).Select
Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
Next
sh.Select
cel.Select
ActiveWindow.ScrollRow = SRw
ActiveWindow.ScrollColumn = SCol
Application.ScreenUpdating = True
End Sub
Sub Tidy()
Dim i As Long
Application.ScreenUpdating = False
For i = Sheets.Count To 1 Step -1
If Not IsChart(Sheets(i).Name) Then
Sheets(i).Select
Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End If
Next
Application.ScreenUpdating = True
End Sub
Function IsChart(cName As String) As Boolean
Dim tmpChart As Chart
On Error Resume Next
Set tmpChart = Charts(cName)
IsChart = IIf(tmpChart Is Nothing, False, True)
End Function
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Tidy
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
TidyAndReturn
End Sub
|