Macro Error Help Needed

skidragon02 · Feb 14, 2013

Hello,

I am trying to use the following macro to simultaneously format a bunch of tabs and running into problems. Can anybody tell me what is going wrong? Thanks in advance for your help!

[pre]

Code:

Sub Column_Widths()
&#39;
&#39; Column_Widths Macro
&#39;
&#39; select the sheets I want
&#39;
&#39;Sheets(&#34;767 DE 1-2 Lates&#34;).Select
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Sheets:=1
&#39;   ActiveWindow.ScrollWorkbookTabs Position:=xlLast
&#39;   ActiveWindow.ScrollWorkbookTabs Position:=xlLast
&#39;Sheets(Array(&#34;Sheet3&#34;, &#34;Sheet4&#34;, &#34;Sheet5&#34;, &#34;Sheet6&#34;, &#34;Sheet7&#34;, &#34;Sheet8&#34;, &#34;Sheet9&#34;, &#34;Sheet10&#34;, &#34;Sheet11&#34;, &#34;Sheet12&#34;, &#34;Sheet13&#34;, &#34;Sheet14&#34;, &#34;Sheet15&#34;, &#34;Sheet16&#34;, &#34;Sheet17&#34;, &#34;Sheet18&#34;, &#34;Sheet19&#34;, &#34;Sheet20&#34;, &#34;Sheet21&#34;, &#34;Sheet22&#34;, &#34;Sheet23&#34;, &#34;Sheet24&#34;, &#34;Sheet25&#34;, &#34;Sheet26&#34;, &#34;Sheet27&#34;, &#34;Sheet28&#34;, &#34;Sheet29&#34;)).Select
Sheets(Array(&#34;Sheet3&#34;, &#34;Sheet4&#34;)).Select
&#39;    Sheets(&#34;767 DE 1-2 Lates&#34;).Activate
&#39;    Sheets(Array(&#34;767-777 DE CG 88-48 Current Del&#34;, &#34;767-777 DE CG 88-48 Cum Del&#34;)). _
&#39;        Select Replace:=False
&#39; Adjust column sizes
Columns(&#34;A:A&#34;).ColumnWidth = 5.57
Columns(&#34;B:B&#34;).ColumnWidth = 11.71
Columns(&#34;C:C&#34;).ColumnWidth = 8.86
Columns(&#34;D:D&#34;).ColumnWidth = 8.86
Columns(&#34;E:E&#34;).ColumnWidth = 9.43
Columns(&#34;F:F&#34;).ColumnWidth = 2.86
Columns(&#34;G:G&#34;).ColumnWidth = 17#
Columns(&#34;H:H&#34;).ColumnWidth = 7.29
Columns(&#34;I:I&#34;).ColumnWidth = 32#
Columns(&#34;J:J&#34;).ColumnWidth = 32#
Columns(&#34;K:K&#34;).ColumnWidth = 16.29
Columns(&#34;L:L&#34;).ColumnWidth = 7.71
Columns(&#34;M:M&#34;).ColumnWidth = 10.29
Columns(&#34;N:N&#34;).ColumnWidth = 4.86
Columns(&#34;O:O&#34;).ColumnWidth = 7.14
Columns(&#34;P:P&#34;).ColumnWidth = 4.86
Columns(&#34;Q:Q&#34;).ColumnWidth = 6.29
Columns(&#34;R:R&#34;).ColumnWidth = 5.57
Rows(&#34;3:103&#34;).EntireRow.AutoFit
End Sub

[/pre]

Luke M · Feb 14, 2013

You had a lot of commented lines in your macro which I've removed for clarity. I believe the structure of this macro is what you're looking for.

[pre]

Code:

Sub Column_Widths()

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Sheets(Array(&#34;Sheet3&#34;, &#34;Sheet4&#34;))
With ws
.Columns(&#34;A:A&#34;).ColumnWidth = 5.57
.Columns(&#34;B:B&#34;).ColumnWidth = 11.71
.Columns(&#34;C:C&#34;).ColumnWidth = 8.86
.Columns(&#34;D:D&#34;).ColumnWidth = 8.86
.Columns(&#34;E:E&#34;).ColumnWidth = 9.43
.Columns(&#34;F:F&#34;).ColumnWidth = 2.86
.Columns(&#34;G:G&#34;).ColumnWidth = 17#
.Columns(&#34;H:H&#34;).ColumnWidth = 7.29
.Columns(&#34;I:I&#34;).ColumnWidth = 32#
.Columns(&#34;J:J&#34;).ColumnWidth = 32#
.Columns(&#34;K:K&#34;).ColumnWidth = 16.29
.Columns(&#34;L:L&#34;).ColumnWidth = 7.71
.Columns(&#34;M:M&#34;).ColumnWidth = 10.29
.Columns(&#34;N:N&#34;).ColumnWidth = 4.86
.Columns(&#34;O:O&#34;).ColumnWidth = 7.14
.Columns(&#34;P:P&#34;).ColumnWidth = 4.86
.Columns(&#34;Q:Q&#34;).ColumnWidth = 6.29
.Columns(&#34;R:R&#34;).ColumnWidth = 5.57
.Rows(&#34;3:103&#34;).EntireRow.AutoFit
End With
Next ws
Application.ScreenUpdating = True
End Sub

[/pre]

skidragon02 · Feb 14, 2013

I need sheets 3-29, so I wrote it like this after your suggestion:

Sub Column_Widths()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Sheets(Array("Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24", "Sheet25", "Sheet26", "Sheet27", "Sheet28", "Sheet29"))

ws.Select

Columns("A:A").ColumnWidth = 5.57

Columns("B:B").ColumnWidth = 11.71

Columns("C:C").ColumnWidth = 8.86

Columns("D:D").ColumnWidth = 8.86

Columns("E:E").ColumnWidth = 9.43

Columns("F:F").ColumnWidth = 2.86

Columns("G:G").ColumnWidth = 17#

Columns("H:H").ColumnWidth = 7.29

Columns("I:I").ColumnWidth = 32#

Columns("J:J").ColumnWidth = 32#

Columns("K:K").ColumnWidth = 16.29

Columns("L:L").ColumnWidth = 7.71

Columns("M:M").ColumnWidth = 10.29

Columns("N:N").ColumnWidth = 4.86

Columns("O:O").ColumnWidth = 7.14

Columns("P

").ColumnWidth = 4.86

Columns("Q:Q").ColumnWidth = 6.29

Columns("R:R").ColumnWidth = 5.57

Rows("3:103").EntireRow.AutoFit

Next ws

Application.ScreenUpdating = True

End Sub

Now it says, "Run-time error '9': Subscript out of range"...

Luke M · Feb 14, 2013

Can you tell us what line gave you that error? All the error tells me is that it tried to select something that's not available. All of your sheets actually exist? If so, one way to eliminate possible human error is to not type out all the sheets, like so:

[pre]

Code:

Sub Column_Widths()

Dim ws As Worksheet
Application.ScreenUpdating = False
For i = 3 To 29
With Worksheets(&#34;Sheet&#34; & i)
.Columns(&#34;A:A&#34;).ColumnWidth = 5.57
.Columns(&#34;B:B&#34;).ColumnWidth = 11.71
.Columns(&#34;C:C&#34;).ColumnWidth = 8.86
.Columns(&#34;D:D&#34;).ColumnWidth = 8.86
.Columns(&#34;E:E&#34;).ColumnWidth = 9.43
.Columns(&#34;F:F&#34;).ColumnWidth = 2.86
.Columns(&#34;G:G&#34;).ColumnWidth = 17#
.Columns(&#34;H:H&#34;).ColumnWidth = 7.29
.Columns(&#34;I:I&#34;).ColumnWidth = 32#
.Columns(&#34;J:J&#34;).ColumnWidth = 32#
.Columns(&#34;K:K&#34;).ColumnWidth = 16.29
.Columns(&#34;L:L&#34;).ColumnWidth = 7.71
.Columns(&#34;M:M&#34;).ColumnWidth = 10.29
.Columns(&#34;N:N&#34;).ColumnWidth = 4.86
.Columns(&#34;O:O&#34;).ColumnWidth = 7.14
.Columns(&#34;P:P&#34;).ColumnWidth = 4.86
.Columns(&#34;Q:Q&#34;).ColumnWidth = 6.29
.Columns(&#34;R:R&#34;).ColumnWidth = 5.57
.Rows(&#34;3:103&#34;).EntireRow.AutoFit
End With
Next ws
Application.ScreenUpdating = True
End Sub

[/pre]

skidragon02 · Feb 14, 2013

Yes, it was Line 5.

skidragon02 · Feb 14, 2013

Running this new one gives me an error message saying, "Compile Error: Invalid Next control variable reference".

Luke M · Feb 14, 2013

Oops, forgot to change the last line. My apologies. Need to chang the "Next ws" to just "Next".

[pre]

Code:

Sub Column_Widths()

Dim ws As Worksheet
Application.ScreenUpdating = False
For i = 3 To 29
With Worksheets(&#34;Sheet&#34; & i)
.Columns(&#34;A:A&#34;).ColumnWidth = 5.57
.Columns(&#34;B:B&#34;).ColumnWidth = 11.71
.Columns(&#34;C:C&#34;).ColumnWidth = 8.86
.Columns(&#34;D:D&#34;).ColumnWidth = 8.86
.Columns(&#34;E:E&#34;).ColumnWidth = 9.43
.Columns(&#34;F:F&#34;).ColumnWidth = 2.86
.Columns(&#34;G:G&#34;).ColumnWidth = 17#
.Columns(&#34;H:H&#34;).ColumnWidth = 7.29
.Columns(&#34;I:I&#34;).ColumnWidth = 32#
.Columns(&#34;J:J&#34;).ColumnWidth = 32#
.Columns(&#34;K:K&#34;).ColumnWidth = 16.29
.Columns(&#34;L:L&#34;).ColumnWidth = 7.71
.Columns(&#34;M:M&#34;).ColumnWidth = 10.29
.Columns(&#34;N:N&#34;).ColumnWidth = 4.86
.Columns(&#34;O:O&#34;).ColumnWidth = 7.14
.Columns(&#34;P:P&#34;).ColumnWidth = 4.86
.Columns(&#34;Q:Q&#34;).ColumnWidth = 6.29
.Columns(&#34;R:R&#34;).ColumnWidth = 5.57
.Rows(&#34;3:103&#34;).EntireRow.AutoFit
End With
Next
Application.ScreenUpdating = True
End Sub

[/pre]

skidragon02 · Feb 14, 2013

It now says "Run-time error '9': Subscript out of range" in reference to Line 6.

skidragon02 · Feb 15, 2013

Solution Found!

[pre]

Code:

Sub Column_Widths()

Dim ws As Worksheet
Application.ScreenUpdating = False
For i = 3 To 29
With Sheets(i)
.Columns(&#34;A:A&#34;).ColumnWidth = 5.57
.Columns(&#34;B:B&#34;).ColumnWidth = 11.71
.Columns(&#34;C:C&#34;).ColumnWidth = 8.86
.Columns(&#34;D:D&#34;).ColumnWidth = 8.86
.Columns(&#34;E:E&#34;).ColumnWidth = 9.43
.Columns(&#34;F:F&#34;).ColumnWidth = 2.86
.Columns(&#34;G:G&#34;).ColumnWidth = 17#
.Columns(&#34;H:H&#34;).ColumnWidth = 7.29
.Columns(&#34;I:I&#34;).ColumnWidth = 32#
.Columns(&#34;J:J&#34;).ColumnWidth = 32#
.Columns(&#34;K:K&#34;).ColumnWidth = 16.29
.Columns(&#34;L:L&#34;).ColumnWidth = 7.71
.Columns(&#34;M:M&#34;).ColumnWidth = 10.29
.Columns(&#34;N:N&#34;).ColumnWidth = 4.86
.Columns(&#34;O:O&#34;).ColumnWidth = 7.14
.Columns(&#34;P:P&#34;).ColumnWidth = 4.86
.Columns(&#34;Q:Q&#34;).ColumnWidth = 6.29
.Columns(&#34;R:R&#34;).ColumnWidth = 5.57
.Rows(&#34;3:103&#34;).EntireRow.AutoFit
End With
Next
Application.ScreenUpdating = True
End Sub

[/pre]

Luke M · Feb 15, 2013

Glad you figured it out, and thanks for sharing the solution with us. =)

Macro Error Help Needed

skidragon02

New Member

Luke M

Excel Ninja

skidragon02

New Member

Luke M

Excel Ninja

skidragon02

New Member

skidragon02

New Member

Luke M

Excel Ninja

skidragon02

New Member

skidragon02

New Member

Luke M

Excel Ninja