VBA to auto-resize worksheet to fill the screen

[leopardhawk]

Hello forum friends, this is my second attempt to solve this, the last one ended badly. By badly, I mean with me just a confused simpleton. I'm thinking that what I am trying to do can't be all that difficult but I can't get it to work. Could really use some help here.

I have a workbook with 12 worksheets, all of them are of varying widths as far as the number and size of the columns. My goal is to have the workbook open on anyone's computer and 'fill the screen' horizontally, all the columns with data from left to right, no white space, especially to the right side. I don't care about the vertical aspect because the users can easily scroll up and down. I just don't want them to have to scroll left or right. I found this little piece of code but when I email the workbook to a different computer, the columns with data are all 'selected' but there are still five empty columns to the right.

Private Sub Workbook_Open()
With Sheets("Sheet18")
Columns("A:L").Select
ActiveWindow.Zoom = True
Range("A1").Select
End With
End Sub

Also, I have 12 worksheets that are using anywhere from 5 to 20 columns so I'm not sure how to incorporate that fact into the code either. I was just trying to get it to work with this one worksheet to start. Any help is much appreciated. Thanks!

 

[leopardhawk]

I've recorded a macro to select the cells from Column A to the right so if there is more than column L it will include them. If you add the code behind each sheet you want to zoom to then it will work for you. Start by right clicking one of the sheets and select View Code then change the two drop down to do use the following, then whilst in the VBA screen just double click each sheet name and add the code so the code will be behind each sheet.

Private Sub Worksheet_Activate()
Range(Selection, Selection.End(xlToRight)).Select
    ActiveWindow.Zoom = True
    Range("A1").Select
End Sub

With this code, two of the worksheets were zoomed as large as they could get and the rest of them were zoomed as small as they could get. I'm confused...

 

[Jaafar Tribak]

@Jaafar Tribak I am getting a 'compile error, expected function or variable'. The 'private sub workbook' line is highlighted in yellow and the '.GoTo' is highlighted in blue.

Strange!

Do you have any other code in the ThisWorkbook module ?

This is the code. Try copying and pasting it in the thisworkbook module as is :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Dim lRightMostColumnIndex  As Long
 
    Application.ScreenUpdating = False
 
    With Sh
        lRightMostColumnIndex = .UsedRange.SpecialCells(xlCellTypeLastCell).Column
        .Range(.Columns(1), .Columns(lRightMostColumnIndex)).Select
        ActiveWindow.Zoom = True
        Application.Goto .Range("A1"), True
    End With

End Sub

If the above code doesn't work, I suspect there must be something going on elsewhere in your vba project.

Can you upload a copy of your workbook to file sharing site and post a link here so we can take a look ?

 

[leopardhawk]

@Jaafar Tribak Hi Jaafar, thanks for getting back to me. To answer your question, no, I do not have any other code in the ThisWorkbook module. I will try again and report back to you.

 

[leopardhawk]

@Jaafar Tribak ok, this time I did not get the error so I suspect I did something wrong the first time. I might have copied your code into Workbook_Open instead of Workbook_SheetActivate...?? Anyway, no error now but the code is doing some interesting things. As I said, there are 12 worksheets (that are visible to my users) and another 8 that are VeryHidden. The hidden worksheets are PowerQuery's to pull data from a website that I control.

I think we might be on the right track but let me tell you what I am seeing now by worksheet for the ones that are visible to the users...

Sheet19 is zoomed to almost as large as it can get and it's the only one like that which is very strange. There is an image that I am using as a background because this worksheet is my 'Home' page, just trying to make it look appealing but it only covers A1:Q22.
Sheets 1, 5, 11 & 18 all have 3 columns showing on the right side.
Sheets 9 & 12 have 1 column showing
Sheet3 has 10 columns showing
Sheet10 has 11 columns showing
Sheets 4, 6, & 8 are all zoomed horizontally perfectly (this is what I was hoping for with all the worksheets.

I did as you suggested in post #8 and I went through each worksheet to make sure there was NO formatting to the right side of the last column used. Any ideas?

 

[Jaafar Tribak]

Difficult to tell without seeing the workbook.
Maybe someone else in the forum can see this thread and figure out what might be causing the worksheets view not to adjust.