Macro to zoom to fit window, then apply that zoom factor to other sheets

[Gerald Higgins]

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

 

[cornflakegirl]

After you've auto-zoomed, set a variable eg iZoom = ActiveWindow.Zoom

Then on other sheets just set ActiveWindow.Zoom = iZoom

 

[Gerald Higgins]

Thanks very much cornflakegirl.

I should have also said that I know virtually nothing about macros - most of the ones I use are built up using the recorder, and I don't really understand what the coding does.

I've tried doing what you suggested but couldn't get it to work.

Here's the code

Dim Izoom As ActiveWindow.Zoom
    Sheets("COLLABORATE").Select
    Set ActiveWindow.Zoom = Izoom

I'm sure I'm making a very stupid mistake here - can you tell me what it is please ?

 

[cornflakegirl]

Um... not sure what you're doing with your Dim statement...

Something like:

Dim iZoom as integer
Sheets("WELCOME").Select
Range("A1:N18").Select
ActiveWindow.Zoom = True
iZoom = ActiveWindow.Zoom
 
Sheets("COLLABORATE").Select
ActiveWindow.Zoom = iZoom

 

[Gerald Higgins]

Um... not sure what you're doing with your Dim statement...

Well like I said, I really don't know what I'm doing !

But thanks very much - your code works perfectly !

 

[cornflakegirl]

My (limited) understanding of Dim statements is that they're just to tell the macro what type of variable you're creating - you can't do anything functional with them.

(Why the new disclaimer on your sig? Was there actual confusion? I really like the "another day" one btw - would have voted for it in the poll if it had been there )

 

[Smitty]

Gerald,

This may be an option, but note that there might be some problems with Using Range depending on where you want to zoom.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetActivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)<br>    ActiveSheet.UsedRange.Select<br>    ActiveWindow.Zoom = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Hope that helps,

Why the new disclaimer on your sig? Was there actual confusion?

Sadly, yes.

 

[Gerald Higgins]

Thanks cornflakegirl and pennysaver.

Cornflakegirl's suggestion does seem to do what I want.

I will take a look at pennysaver's as well, to see if it does something different.

Was there actual confusion about the signature ?
Yes indeed . . .
<TABLE style="WIDTH: 54pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=72 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=72 height=17>http://www.mrexcel.com/forum/showthread.php?t=314320</TD></TR></TBODY></TABLE>