Results 1 to 7 of 7

Thread: Insert Column with the same format + size as rest of tab
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

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

    Code:
    Sub philb99_3()
        Dim x As Long
        x = 1
        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 by Yongle; Jul 26th, 2019 at 01:23 PM.

  5. #5
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,056
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

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

    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

  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Perfect - thank you

    Is there a way to ensure

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •