EXCEL, apply stylesheet to different excel files

Question

Currently I have a job (which one of the task) involve just simply applying same style, same format for different excel files.

I would like to find out the way of simplify it.

This style sheet (or sort of think) will need to.

1) Add empty line to very top of the excel file
2) A1-F2 make bold
3) A1-F3 Make full borders
4) A1-F3 Auto Fit Column Width
5) A2-F2 Make colour GREY

I will need to apply same style to large amount of files every day. Looking forward for simple solution.

Answer 1

You can use the MACRO recoder to start with.

Anyway, try the code below (it will format it for "Sheet1" (modify to your requested sheet name).

If you want to apply it to all Sheets, then you need to loop through all sheets in workbook.

Option Explicit

Sub ApplyExcelShtFormat()

Dim Sht             As Worksheet

' change Sheet name to your needs
Set Sht = ThisWorkbook.Sheets("Sheet1")

With Sht
    ' add 1 Row above the first row
    .Rows("1:1").Insert Shift:=xlDown

    ' modify font to bold
    .Range("A1:F2").Font.Bold = True

    ' add borders all around
    .Range("A1:F3").BorderAround xlContinuous, xlThin

    ' add internal borders
    With .Range("A1:F3").Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With .Range("A1:F3").Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    ' columns auto fit
    .Range("A1:F3").EntireColumn.AutoFit

    ' cell interior color grey (change number according to your kind of gray)
    .Range("A2:F2").Interior.Color = 9868950
End With

End Sub