Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Randy11

Technical User
Oct 4, 2002
175
CA
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: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
 
The following should work...
Code:
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.
 
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Thank you. That was a bit like forest for the trees. Appreciate your response.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top