Nested Arrays

[Matt]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>
>
>
>

[Matt]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>