I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks

try following (not tested)

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Visible = True
If .FilterMode = True Then FilterMode = False
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub
--
jb


"Yossy" wrote:

I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks

Thanks John,
It does remove the freeze and splits if any but does not work for the filter
and hidden rows.


"john" wrote:

try following (not tested)

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Visible = True
If .FilterMode = True Then FilterMode = False
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub
--
jb


"Yossy" wrote:

I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks

try updated version:

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Unprotect Password:="mypassword" '<< change as required
.Visible = True
If .FilterMode = True Then .ShowAllData
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub

I have added a line to unprotect sheet delete if not needed otherwise add
correct password.
--
jb


"Yossy" wrote:

Thanks John,
It does remove the freeze and splits if any but does not work for the filter
and hidden rows.


"john" wrote:

try following (not tested)

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Visible = True
If .FilterMode = True Then FilterMode = False
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub
--
jb


"Yossy" wrote:

I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks

Perfect, works great. Is there a way to incorporate Format cell in this. That
is to clear wrap text,shrink to fit and merge cell.

Sorry for asking too much. I will really appreciate all help. Please note i
do get hundreds of sheets with multple tabs in each sheet. Just want to run
macro so that it will affect all sheets and tabs in a folder.

"john" wrote:

try updated version:

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Unprotect Password:="mypassword" '<< change as required
.Visible = True
If .FilterMode = True Then .ShowAllData
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub

I have added a line to unprotect sheet delete if not needed otherwise add
correct password.
--
jb


"Yossy" wrote:

Thanks John,
It does remove the freeze and splits if any but does not work for the filter
and hidden rows.


"john" wrote:

try following (not tested)

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Visible = True
If .FilterMode = True Then FilterMode = False
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub
--
jb


"Yossy" wrote:

I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks

yes is the short answer but depending on number of sheets may prove to be
rather slow. I am required elsewhere at moment if i get an opportunity will
have think about your request unless another person kindly responds.

--
jb


"Yossy" wrote:

Perfect, works great. Is there a way to incorporate Format cell in this. That
is to clear wrap text,shrink to fit and merge cell.

Sorry for asking too much. I will really appreciate all help. Please note i
do get hundreds of sheets with multple tabs in each sheet. Just want to run
macro so that it will affect all sheets and tabs in a folder.

"john" wrote:

try updated version:

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Unprotect Password:="mypassword" '<< change as required
.Visible = True
If .FilterMode = True Then .ShowAllData
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub

I have added a line to unprotect sheet delete if not needed otherwise add
correct password.
--
jb


"Yossy" wrote:

Thanks John,
It does remove the freeze and splits if any but does not work for the filter
and hidden rows.


"john" wrote:

try following (not tested)

Sub clearsettings()
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
With sh
.Activate
.Visible = True
If .FilterMode = True Then FilterMode = False
End With
With ActiveWindow
.FreezePanes = False
.Split = False
End With
Next sh
Application.ScreenUpdating = True
End Sub
--
jb


"Yossy" wrote:

I usually get a lot of files to work with and most of the time there can be
hidden sheets, freezed panes, split and filtered values.

Is there a way to run a macro and make sure that all my sheets in the folder
have been cleared of these functions before I can proceed.
will appreciate all help. Thanks