View Full Version : Solved: Zoom and Setting Column Sizes
Nurofen
10-14-2007, 06:19 AM
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
10-14-2007, 07:06 AM
Two minor changes;
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
Nurofen
10-14-2007, 07:15 AM
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
10-14-2007, 07:16 AM
Maybe this is better?
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
Nurofen
10-14-2007, 07:19 AM
Same error Gert Jan,
paulked
10-14-2007, 07:21 AM
Same error Gert Jan,
Works for me :dunno
Gert Jan
10-14-2007, 07:32 AM
It works for me to,
Can you post your workbook?
lucas
10-14-2007, 07:42 AM
This works for me also:
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
Nurofen
10-14-2007, 07:42 AM
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
10-14-2007, 07:46 AM
Try actually referencing the worksheet when trying to refer to the columns.
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
Gert Jan
10-14-2007, 07:52 AM
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
10-14-2007, 07:56 AM
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
10-14-2007, 07:57 AM
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:confused:
Ok I don't feel so silly now. lol
lucas
10-14-2007, 08:09 AM
The locals window shows it cycling through the sheets and I am getting no errors....
lucas
10-14-2007, 08:13 AM
Norie has caught it....this works for me:
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
Gert Jan
10-14-2007, 08:15 AM
I only checked first and last sheet, they were ok.
I think Norie is right, this works for all sheets
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
Gert Jan
10-14-2007, 08:15 AM
Steve, you're to fast for me....:rotlaugh:
Nurofen
10-14-2007, 08:24 AM
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
10-14-2007, 08:28 AM
Team effort. cudos to Norie
Nurofen
10-14-2007, 08:29 AM
Thank you Norie:thumb
Nurofen
Norie
10-14-2007, 10:27 AM
Nurofen
How would you be defining those?
In the example workbook the first 5 sheets are FRONT SHEET, Mon 01-10-07 to Thu 04-10-07.
By the way you do realise all of this could be done with one button.
And also that you could actually copy the master worksheet when creating the new sheets rather than creating blank worksheets.
Nurofen
10-14-2007, 11:23 AM
Hi Norie,
On the main Workbook, I have 3 hidden sheets 1)vlookupdata1 2)vlookupdata2 3)vlookupdata3 4)Home Page 5)Master Copy.
I've tried to put it altogether but kept getting it wrong, but cause it worked I just left it alone.
But if you don mind explaining how, that will be great
Thank for your time in helping me
Nurofen
Norie
10-14-2007, 12:52 PM
Nurofen
Explain what exactly?
Nurofen
10-14-2007, 01:10 PM
Hi Norie
"By the way you do realise all of this could be done with one button.
And also that you could actually copy the master worksheet when creating the new sheets rather than creating blank worksheets."
The above please sorry for not being clear
Thank you for your time
Nurofen
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.