PDA

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