Solved: Zoom and Setting Column Sizes

[Nurofen]

Hi,

I have this code which zooms all my sheets to 75% but i'm unable to get it set column sizes, can someone tell me what i'm doing wrong.

Sub MyMacro12()

Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
  ws.Activate
 
     ActiveWindow.Zoom = 75
   
'ActiveWindow("A:A").EntireColumn.AutoFit  
 'Columns("B:B").ColumnWidth = 10.29
'Columns("G:G").ColumnWidth = 11.86
'Columns("H:H").ColumnWidth = 12
'Columns("I:I").ColumnWidth = 11.71
 'Columns("J:J").ColumnWidth = 11.29
'Columns("S:S").ColumnWidth = 12
'Columns("T:T").ColumnWidth = 12.86
 ' Columns("U:U").ColumnWidth = 2.29
   
   
Next ws


Application.ScreenUpdating = False
End Sub

Thank you for your time

Nurofen

[Gert Jan]

Two minor changes;

[vba]Sub MyMacro12()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate

ActiveWindow.Zoom = 75

Columns("A:A").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 10.29
Columns("G:G").ColumnWidth = 11.86
Columns("H:H").ColumnWidth = 12
Columns("I:I").ColumnWidth = 11.71
Columns("J:J").ColumnWidth = 11.29
Columns("S:S").ColumnWidth = 12
Columns("T:T").ColumnWidth = 12.86
Columns("U:U").ColumnWidth = 2.29


Next ws

Application.ScreenUpdating = True
End Sub[/vba]

[Nurofen]

Hi Gert Jan,

Thanks for helping, but that bring up.

Run-time error'1004':

Autofit method of Range class failed


Thank you for your time

Nurofen

[Gert Jan]

Maybe this is better?

[VBA]Sub MyMacro12()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.Zoom = 75
Columns("A").Columns.AutoFit
Columns("B").ColumnWidth = 10.29
Columns("G").ColumnWidth = 11.86
Columns("H").ColumnWidth = 12
Columns("I").ColumnWidth = 11.71
Columns("J").ColumnWidth = 11.29
Columns("S").ColumnWidth = 12
Columns("T").ColumnWidth = 12.86
Columns("U").ColumnWidth = 2.29
Next ws
Application.ScreenUpdating = True
End Sub[/VBA]

[Nurofen]

Same error Gert Jan,

[paulked]

Same error Gert Jan,

Works for me

[Gert Jan]

It works for me to,
Can you post your workbook?

[lucas]

This works for me also:
[VBA]Option Explicit
Sub MyMacro12()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.Zoom = 75
Columns("A:A").Columns.AutoFit
Columns("B:B").ColumnWidth = 10.29
Columns("G:G").ColumnWidth = 11.86
Columns("H:H").ColumnWidth = 12
Columns("I:I").ColumnWidth = 11.71
Columns("J:J").ColumnWidth = 11.29
Columns("S:S").ColumnWidth = 12
Columns("T:T").ColumnWidth = 12.86
Columns("U:U").ColumnWidth = 2.29
Next ws
Application.ScreenUpdating = False
End Sub[/VBA]

[Nurofen]

hi,

I'm not sure what i'm doing wrong.

On the Front Sheet Click buttons 1 2 3 in order, the zoom works but not the columns sizes

Sorry for Values update, just cancel a few times, its happens when you run button 2

Thank you for your help

Nurofen

[Norie]

Try actually referencing the worksheet when trying to refer to the columns.
[vba]
With ws
.Activate
ActiveWindow.Zoom = 75
.Columns("A").Columns.AutoFit
.Columns("B").ColumnWidth = 10.29
.Columns("G").ColumnWidth = 11.86

.Columns("H").ColumnWidth = 12
.Columns("I").ColumnWidth = 11.71
.Columns("J").ColumnWidth = 11.29
.Columns("S").ColumnWidth = 12
.Columns("T").ColumnWidth = 12.86
.Columns("U").ColumnWidth = 2.29
End With[/vba]

[Gert Jan]

hi,

I'm not sure what i'm doing wrong.

On the Front Sheet Click buttons 1 2 3 in order, the zoom works but not the columns sizes

Sorry for Values update, just cancel a few times, its happens when you run button 2

Thank you for your help

Nurofen

I've tried your workbook and the columns are being resized, no errors???

[lucas]

hmm.....going to have to give Nurofen some backing here....it doesn't seem to work for me either and I don't know why yet. It zooms to 75 ok but that's it.

[Nurofen]

Hi Gert Jan,

Now I'm confused, I can't get it to work on this side of world.
I don't know what to say.


Nurofen

Ok I don't feel so silly now. lol

[lucas]

The locals window shows it cycling through the sheets and I am getting no errors....

[lucas]

Norie has caught it....this works for me:
[VBA]Private Sub CommandButton3_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ws
.Activate
ActiveWindow.Zoom = 75
.Columns("A").Columns.AutoFit
.Columns("B").ColumnWidth = 10.29
.Columns("G").ColumnWidth = 11.86

.Columns("H").ColumnWidth = 12
.Columns("I").ColumnWidth = 11.71
.Columns("J").ColumnWidth = 11.29
.Columns("S").ColumnWidth = 12
.Columns("T").ColumnWidth = 12.86
.Columns("U").ColumnWidth = 2.29
End With
Next ws
Application.ScreenUpdating = False
End Sub[/VBA]

[Gert Jan]

I only checked first and last sheet, they were ok.

I think Norie is right, this works for all sheets
[VBA]Private Sub CommandButton3_Click()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveWindow.Zoom = 75
With ws
.Columns("A").Columns.AutoFit
.Columns("B").ColumnWidth = 10.29
.Columns("G").ColumnWidth = 11.86
.Columns("H").ColumnWidth = 12
.Columns("I").ColumnWidth = 11.71
.Columns("J").ColumnWidth = 11.29
.Columns("S").ColumnWidth = 12
.Columns("T").ColumnWidth = 12.86
.Columns("U").ColumnWidth = 2.29
End With
Next ws
Application.ScreenUpdating = True
End Sub[/VBA]

[Gert Jan]

Steve, you're to fast for me....

[Nurofen]

Thank you for help- it works

How do I stop it zooming and sizing the 1st five sheets


Thank you for your time

Nurofen

[lucas]

Team effort. cudos to Norie

[Nurofen]

Thank you Norie

Nurofen