Excel 2007 VBA - Set Column Widths All Worksheets in Workbook plus

[Randy11]

Hi Am trying to apply column widths to all worksheets in a workbook & then add 1 row at top of all worksheets except for the one named Master. Assistance Appreciated.

Am struggling getting this code to affect all sheets, seems only to impact the 1st sheet in the workbook? Appears to run but formatting does not apply to all visible sheets.

Sub Select_Each_Sheet()

Dim ws As Worksheet

For Each ws In Sheets

If ws.Visible Then ws.Select (False)

Next

Columns("A:A").ColumnWidth = 16
Columns("B:B").ColumnWidth = 19.86
Columns("C:C").ColumnWidth = 7.86
Columns("D").ColumnWidth = 18.71
Columns("E:E").ColumnWidth = 8.71
Columns("F:F").ColumnWidth = 13.14
Columns("G:G").ColumnWidth = 8.57
Columns("H:H").ColumnWidth = 14.71
Columns("I:I").ColumnWidth = 16.86
Columns("J:J").ColumnWidth = 22.57
Columns("K:K").ColumnWidth = 20.29
Columns("L:L").ColumnWidth = 43.86
Columns("V:V").ColumnWidth = 12.29

'Would also like to add a line here that will add a row to all worksheets except
'for the one Named "Master"

'In all sheets except "Master" place text in Cell A1 "This is the Top of the Worksheet"


End Sub

 

[trevil620]

The following should work...

Option Explicit
Sub Select_Each_Sheet()
 
Dim ws As Worksheet
 
    For Each ws In ThisWorkbook.Sheets
        If ws.Visible Then ws.Select (False)
 
        If ws.Name <> "Master" Then
            'Would also like to add a line here that will add a row to all worksheets except
            'for the one Named "Master"
            ws.Rows(1).EntireRow.Insert
            
            'In all sheets except "Master" place text in Cell A1 "This is the Top of the Worksheet"
            ws.Cells(1, 1).value = "This is the Top of the Worksheet"
            
        End If
    
        Columns("A:A").ColumnWidth = 16
        Columns("B:B").ColumnWidth = 19.86
        Columns("C:C").ColumnWidth = 7.86
        Columns("D:D").ColumnWidth = 18.71
        Columns("E:E").ColumnWidth = 8.71
        Columns("F:F").ColumnWidth = 13.14
        Columns("G:G").ColumnWidth = 8.57
        Columns("H:H").ColumnWidth = 14.71
        Columns("I:I").ColumnWidth = 16.86
        Columns("J:J").ColumnWidth = 22.57
        Columns("K:K").ColumnWidth = 20.29
        Columns("L:L").ColumnWidth = 43.86
        Columns("V:V").ColumnWidth = 12.29

    Next ws
 
End Sub

When all else fails, manipulate the data.

 

[Randy11]

Have found this adds the row desired but does not apply the column width formatting. No errors, just does not apply the formatting. If the formatting of the Master sheet could be applied to all other visible worksheets in the workbook this would be great. Am having difficulty without specifically naming sheets in the code. Ideas appreciate. Thank you

 

[SkipVought]

Hi,

Naturally, none of the columns on each sheet has column widths applied!

Each of the line stating with Columns, should have ws.Columns

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Randy11]

Thank you. That was a bit like forest for the trees. Appreciate your response.