Macro to change view of sheets

  • Thread starter Thread starter Fernando Gomez
  • Start date Start date
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
 
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.
 
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
 
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?
 
Back
Top