unhide, Remove filter, Unfreeze help
 

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

unhide, Remove filter, Unfreeze help
 

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

unhide, Remove filter, Unfreeze help
 

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

unhide, Remove filter, Unfreeze help
 

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

unhide, Remove filter, Unfreeze help
 

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

unhide, Remove filter, Unfreeze help
 

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