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

philb99

Active Member
Joined
Feb 3, 2014
Messages
385
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Column 15 is inserted with same format as column 14
Code:
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 ..

Code:
Sub philb99_2()
    Dim x As Long
    x = 15
    Columns(x).Insert
    With Columns(x).Font
        .Name = "Arial"
        .Size = 8
    End With    
End Sub
 
Upvote 0
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
 
Upvote 0
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Try this

Code:
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
 
Upvote 0
Perfect - thank you

Is there a way to ensure

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

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top