Macro to change view of sheets

F

Fernando Gomez

I have a workbook with about 85 sheets happen that we have to change the
view from Normal to Page break preview wvery month, I could record a macro
(with the recorder), page by page and it works, but could somebody help me
to get a condensed macro, because I have to set another macro to changed
them back to Normal.
Thanks for your help.

Fernando
 
J

J.E. McGimpsey

One way:

Public Sub ToggleViews()
Dim wkSht As Worksheet
Dim nView As Long
Dim sOldActive As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
With ActiveWindow
If .View = xlPageBreakPreview Then
nView = xlNormalView
Else
nView = xlPageBreakPreview
End If
End With
sOldActive = ActiveSheet.Name
For Each wkSht In Worksheets
With wkSht
.Activate
ActiveWindow.View = nView
End With
Next wkSht
Worksheets(sOldActive).Select
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

This will toggle all the sheets to page break preview if the
activewindow's view is normal, and vice versa.
 
F

Fernando Gomez

Thanks for this macro:
I have a problem runing it it stops here:
ActiveWindow.View = nView

with this error
Runtime error 1004
application-defined or Object-defined error
I do not know how to fix it or an idea why is happening

Thanks for your help
 
J

J.E. McGimpsey

When it stops, what is the value of nView?

For my version at least, it should be either 1 (xlNormalView) or 2
(xlPageBreakPreview).

Is the sheet it stops on the first sheet? a hidden sheet?
 
Top