Nested Arrays
 

Hello Everyone

Where I work people have their PC's set up with different screen
resolutions. This means that when my spreadsheets open up zoomed to 100% half
the data is off to the side of the screen. I use a macro to zoom the sheets
so that they can see the data. However where there are a lot of sheets their
ends up being a very long macro.

I came up with the idea of using an array to list the sheets. But now I need
another array that holds the last columns to zoom to. I presume I'd set up
another array with the last columns ColumnArray("Z","AA","AB")

But I can't work out how go get this array in to my code

Has anyone got any ideas on how to do this? Or even solve this problem more
elegantly.

Thanks for reading this far, and in advance for any time you put in to
helping me

My code is below.

Matt



Sub SetUserView()

Dim shEach As Worksheet

Dim ZoomedSize As Integer
Dim strStartSheet, strLastCol As String

strStartSheet = ActiveSheet.Name

strLastCol = "Z"

For Each shEach In ActiveWorkbook.Sheets(Array _
("Sheet1", "Sheet2", "Sheet3"))

Application.StatusBar = "Updating PageSizes ..............."

shEach.Select

Range("A1:" & strLastCol & "1").Select
ActiveWindow.Zoom = True
ZoomedSize = ActiveWindow.Zoom
If ZoomedSize 100 Then
ActiveWindow.Zoom = 100
End If
Range("A1").Select

Next

End Sub

Nested Arrays
 

Sub SetUserView()

Dim shEach As Worksheet

Dim ZoomedSize As Integer
Dim strStartSheet, strLastCol As String
Dim ColumnArray as Variant
strStartSheet = ActiveSheet.Name


ColumnArray = Array("Z","AA","AB")
j = lbound(ColumnArray)
For Each shEach In ActiveWorkbook.Sheets(Array _
("Sheet1", "Sheet2", "Sheet3"))

strLastCol = ColumnArray(j)
j = j + 1
Application.StatusBar = "Updating PageSizes ..............."

shEach.Select

Range("A1:" & strLastCol & "1").Select
ActiveWindow.Zoom = True
ZoomedSize = ActiveWindow.Zoom
If ZoomedSize 100 Then
ActiveWindow.Zoom = 100
End If
Range("A1").Select

Next

End Sub

--
Regards,
Tom Ogilvy


"Matt" wrote in message
...
Hello Everyone

Where I work people have their PC's set up with different screen
resolutions. This means that when my spreadsheets open up zoomed to 100%
half
the data is off to the side of the screen. I use a macro to zoom the
sheets
so that they can see the data. However where there are a lot of sheets
their
ends up being a very long macro.

I came up with the idea of using an array to list the sheets. But now I
need
another array that holds the last columns to zoom to. I presume I'd set up
another array with the last columns ColumnArray("Z","AA","AB")

But I can't work out how go get this array in to my code

Has anyone got any ideas on how to do this? Or even solve this problem
more
elegantly.

Thanks for reading this far, and in advance for any time you put in to
helping me

My code is below.

Matt



Sub SetUserView()

Dim shEach As Worksheet

Dim ZoomedSize As Integer
Dim strStartSheet, strLastCol As String

strStartSheet = ActiveSheet.Name

strLastCol = "Z"

For Each shEach In ActiveWorkbook.Sheets(Array _
("Sheet1", "Sheet2", "Sheet3"))

Application.StatusBar = "Updating PageSizes ..............."

shEach.Select

Range("A1:" & strLastCol & "1").Select
ActiveWindow.Zoom = True
ZoomedSize = ActiveWindow.Zoom
If ZoomedSize 100 Then
ActiveWindow.Zoom = 100
End If
Range("A1").Select

Next

End Sub

Nested Arrays
 

Hi Tom

That's just what I needed

Thank you very much indeed

Matt


"Tom Ogilvy" wrote:


Sub SetUserView()

Dim shEach As Worksheet

Dim ZoomedSize As Integer
Dim strStartSheet, strLastCol As String
Dim ColumnArray as Variant
strStartSheet = ActiveSheet.Name


ColumnArray = Array("Z","AA","AB")
j = lbound(ColumnArray)
For Each shEach In ActiveWorkbook.Sheets(Array _
("Sheet1", "Sheet2", "Sheet3"))

strLastCol = ColumnArray(j)
j = j + 1
Application.StatusBar = "Updating PageSizes ..............."

shEach.Select

Range("A1:" & strLastCol & "1").Select
ActiveWindow.Zoom = True
ZoomedSize = ActiveWindow.Zoom
If ZoomedSize 100 Then
ActiveWindow.Zoom = 100
End If
Range("A1").Select

Next

End Sub

--
Regards,
Tom Ogilvy


"Matt" wrote in message
...
Hello Everyone

Where I work people have their PC's set up with different screen
resolutions. This means that when my spreadsheets open up zoomed to 100%
half
the data is off to the side of the screen. I use a macro to zoom the
sheets
so that they can see the data. However where there are a lot of sheets
their
ends up being a very long macro.

I came up with the idea of using an array to list the sheets. But now I
need
another array that holds the last columns to zoom to. I presume I'd set up
another array with the last columns ColumnArray("Z","AA","AB")

But I can't work out how go get this array in to my code

Has anyone got any ideas on how to do this? Or even solve this problem
more
elegantly.

Thanks for reading this far, and in advance for any time you put in to
helping me

My code is below.

Matt



Sub SetUserView()

Dim shEach As Worksheet

Dim ZoomedSize As Integer
Dim strStartSheet, strLastCol As String

strStartSheet = ActiveSheet.Name

strLastCol = "Z"

For Each shEach In ActiveWorkbook.Sheets(Array _
("Sheet1", "Sheet2", "Sheet3"))

Application.StatusBar = "Updating PageSizes ..............."

shEach.Select

Range("A1:" & strLastCol & "1").Select
ActiveWindow.Zoom = True
ZoomedSize = ActiveWindow.Zoom
If ZoomedSize 100 Then
ActiveWindow.Zoom = 100
End If
Range("A1").Select

Next

End Sub