return to xlnormalview problem

[mrLee]

hi,office developers.

i have this macro just for test.
but not return to xlnormalview on the copied sheet.
i'd very appreciated for any comments what the problem with below macro.
thanks in advance.

Sub Macro1()
Dim ViewMode As Long
ActiveSheet.Select
Range("A1") = 1
Range("D20") = 1
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.Copy AFTER:=ActiveSheet
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
End With
ActiveWindow.View = ViewMode
End Sub

 

[Jim Cone]

Several comments...
1. The active sheet is the selected sheet; so you don't need Select.
2. The ViewMode line is in the wrong place.
It should be immediately after the Range("D20") line.
3. You can step thru the revised code using the F8 key and the
window will return to Normal view. However, if you just
run the code normally - F5 key - then the window does not
return to Normal view. It will work if you introduce a pause
before attempting to reset the view (but that's another story)...
'--
Sub Macro1()
Dim ViewMode As Long
Range("A1") = 1
Range("D20") = 1
ViewMode = ActiveWindow.View
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.Copy AFTER:=ActiveSheet
'pause needed here
ActiveWindow.View = ViewMode
End Sub
--
Jim Cone
Portland, Oregon USA



"mrLee"
<[email protected]>
wrote in message
hi,office developers.

i have this macro just for test.
but not return to xlnormalview on the copied sheet.
i'd very appreciated for any comments what the problem with below macro.
thanks in advance.

Sub Macro1()
Dim ViewMode As Long
ActiveSheet.Select
Range("A1") = 1
Range("D20") = 1
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.Copy AFTER:=ActiveSheet
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
End With
ActiveWindow.View = ViewMode
End Sub

 

[JLGWhiz]

The code that you posted is operating properly as written. The variable
ViewMode is set to the pagebreak view which displays with the shaded area.
Then the xlNormalView is returned for the active sheet, which is the new
added sheet. To return the original ActiveSheet to the xlNormalView, Try
this:


Sub Macro1()
Dim ViewMode As Long
ActiveSheet.Select
shNm = ActiveSheet.Name
Range("A1") = 1
Range("D20") = 1
ActiveWindow.View = xlPageBreakPreview
ActiveSheet.Copy AFTER:=ActiveSheet
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
End With
Sheets(shNm).Activate
ActiveWindow.View = xlNormalView
End Sub