iterating through worksheets
 

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

iterating through worksheets
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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

iterating through worksheets
 

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

iterating through worksheets
 

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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

iterating through worksheets
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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

iterating through worksheets
 

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" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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

iterating through worksheets
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"SteveDB1" wrote in message
...
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

iterating through worksheets
 

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

iterating through worksheets
 

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


"Charlie" wrote:

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