iterating through worksheets

[SteveDB1]

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

 

[Don Guillett]

try

Sub setzoomonallsheets()
For i = 1 To Sheets.Count
With Sheets(i)
.Activate
If .Visible = False Then
.Visible = True
ActiveWindow.Zoom = 70
.Visible = False
Else
ActiveWindow.Zoom = 70
End If
End With
Next i
End Sub

 

[Charlie]

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, the
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]

boy, you know when Don G shows up you're bound to get exactly what's needed.
Thank you very much Don.
It's exactly what the doctor ordered.
Have a great afternoon.

 

[Don Guillett]

Glad to help but don't understand why excel can't do it withOUT selecting.

 

[SteveDB1]

I think why yours works without the .select is because you didn't set the
range back to A1.
That's easily modified though.
I'm just amazed that yours was as simple as it was. I saw that I made the
mistake of doing-
if sheets(i).hidden=true then
sheets(i).visible
......code.......
I'll go from what you have and make my modification to set my range home.
Again, thank you.

 

[Don Guillett]

I didn't mean select a1. That is not necessary. I meant ACTIVATING the
sheet....

 

[Charlie]

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]

Sorry, I forgot to include the Sheet.Activate line:

For Each Sheet In ThisWorkbook.Worksheets
SheetState = Sheet.Visible
Sheet.Visible = xlSheetVisible
Sheet.Activate
ActiveWindow.Zoom = 70
HomeSelect
Sheet.Visible = SheetState
Next