The sheet state can be Visible, Hidden, or VeryHidden. The following code
allows for that possiblilty. Also, if you have windows with frozen panes,
moving the cursor to "A1" won't scroll the sheet back home. I included a sub
that will do that for you. By default it will move the cursor to the frozen
corner, same as if you pressed Ctrl-Home, or you can pass it the desired
address.

Sub Test()
'
Dim Sheet As Worksheet
Dim SheetState As Long
'
For Each Sheet In ThisWorkbook.Worksheets
SheetState = Sheet.Visible
Sheet.Visible = xlSheetVisible
ActiveWindow.Zoom = 70
HomeSelect
Sheet.Visible = SheetState
Next
'
End Sub

Public Sub HomeSelect(Optional HomeAddress As String)
'
' scrolls the window home and moves the cursor back to the frozen corner
(or to
' a designated location) in the active window
'
' Need to select range "A1" first in case there is an object or control,
such as a
' chart, that has the focus
'
Range("A1").Select
ActiveWindow.ActivePane.SmallScroll Up:=Rows.Count, ToLeft:=Columns.Count
If HomeAddress = "" Then
Cells(ActiveWindow.ActivePane.ScrollRow,
ActiveWindow.ActivePane.ScrollColumn).Select
Else
Range(HomeAddress).Select
End If
'
End Sub

P.S. I see you like my display name :)


"Charlie" wrote:


Hi,



Here the code. I added a if statement "Sheets(i).Visible = False".
So if the sheet is hidden, it will unhide it, do the Zoom 70, then
hide it back.


Sub fff()

Dim i As Integer
Dim count As Integer
Dim Rehide As Boolean
count = 1

For i = 1 To Sheets.count
If Sheets(i).Visible = False Then
Sheets(i).Visible = True

Sheets(i).Select
Range("A1").Select
ActiveWindow.Zoom = 70

Sheets(i).Visible = False
Else
Sheets(i).Select
Range("A1").Select
ActiveWindow.Zoom = 70
End If
Next
End Sub

Charles
'Opener Consulting Home' (http://www.openerconsulting.com)

SteveDB1;179087 Wrote:
Hi all.
I've made a macro that iterates through an entire workbook, sets active
cell
to A1, and zooms to 70%.
It works great until I run into a hidden worksheet-- which we have
plenty of
in our workbooks.
Is there a way that I can have it run through all
worksheets(hidden/visible), regardless?

Thank you.

Here's my code----------------------------------------------

Dim i As Integer
Dim count As Integer
count = 1

For i = 1 To Sheets.count
Sheets(i).Select
Range("A1").Select
ActiveWindow.Zoom = 70
Next


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49521