Run macro through all worksheets

[Gliori]

Hello, as the title states I want to run a macro through all worksheets in a workbook.

Here is my code:

Sub test1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Columns("M:M").ColumnWidth = 11.5
Next ws
End Sub

But it does only apply to the active worksheet. Can anyone see what's wrong with it?

Thank you in advance!

Cheers!

 

[My Aswer Is This]

Try This:

Sub test1()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Columns("M:M").ColumnWidth = 11.5
Next
End Sub

 

[Trevor G]

You could just activate the worksheet

Sub test1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.activate
Columns("M:M").ColumnWidth = 11.5
Next ws
End Sub

 

[hiker95]

Gliori,

Here is another macro solution for you to consider, that will do what you have requested without activating each worksheet.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub test1_V2()
' hiker95, 07/01/2015, ME865189
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  ws.Columns("M:M").ColumnWidth = 11.5
Next ws
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the test1_V2 macro.

 

[Gliori]

Alright! Thank you very much Trevor and hiker for taking your time and helping me. I'm not at the office right now but I'll try the codes as soon as I get there and get back to you with the results.

Kind regards!

 

[Gliori]

Both codes works perfect! Thank you guys, appreciated!

 

[hiker95]

Gliori,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.