Insert Column with the same format + size as rest of tab

[philb99]

Hi

I have set excel so all new spreadsheets open with same font and size.

Where I need help is when I insert a new column into other peoples spreadsheets the font changes and the size increases. I have a spreadsheet with a MACRO inserting a new column and I then have to spend time resizing and changing the font in all 15 tabs.

How can I ensure any new column will be the same as my default Arial / 8

Thanks

 

[Yongle]

Column 15 is inserted with same format as column 14

Sub philb99_1()
    Dim x As Long
    x = 15
    Columns(x).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

if format in column to left cannot be used ..

Sub philb99_2()
    Dim x As Long
    x = 15
    Columns(x).Insert
    With Columns(x).Font
        .Name = "Arial"
        .Size = 8
    End With    
End Sub

 

[philb99]

Really appreciate your help but as I am not an expert on Macros can you help further please.

Macro 1 appears to just add a column after Column 14 as does macro 2. I do not require columns added from Col 14, where I need help is when a Column is added to left of Column A the default must be Arial and Size = 8.

At present this defaults to Calibri and Size 11 although all the other columns are Arial / 8 on the spreadsheet

When I open a new excel spreadsheet my default is automatically Arial / 8

Thanks Again

 

[Yongle]

Sub philb99_3()
    Dim x As Long
    x = [COLOR=#ff0000]1[/COLOR]
    Columns(x).Insert
    With Columns(x).Font
        .Name = "Arial"
        .Size = 8
    End With    
End Sub

Incorporate above into your current macro
If still unable to make it work - then post your macro

 

[philb99]

Here is my existing MACRO, as the font does not change - thanks for your help

Sub EnterTeamName()


For Each WS In ActiveWorkbook.Worksheets


WS.Columns(1).Insert
WS.Range("A1").Value = "Team"
WS.Range("A2").Resize(WS.Cells(WS.Rows.Count, "B").End(xlUp).Row).Value = WS.Name
Next WS


End Sub

 

[Yongle]

Try this

Sub EnterTeamName()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .Columns(1).Insert
            .Columns(1).Font.Name = "Arial"
            .Columns(1).Font.Size = 8
            .Range("A1") = "Team"
            .Range("A2").Resize(ws.Cells(ws.Rows.Count, "B").End(xlUp).Row - 1) = .Name
        End With
    Next ws
End Sub

 

[philb99]

Perfect - thank you

Is there a way to ensure

Text = Wrap Text
Alignment - Top Align
Border = All Borders