Macro that formats table - Does not format properly on other sheets with different number of rows

[jefffyluber]

I get a daily report of projects that I work on showing their status. A while ago, I created a macro that cleans up the formatting a bit and makes it more presentable. It works okay; but, each day the number of projects is different or a different sheet has a different number of rows. The macro automatically does the size of the table of when I originally made the macro and creates the formatted table for that many rows. So, sometimes, the table is too long and other times the table is too short.

Any advice on how to fix the code so that it properly does the exact length of the table every time? It looks like the range/row from when I originally did it is hardcoded - so, it doesn't matter of I did CTR + A in the table. The macro doesn't pick that up and do the CTR + A every time.

I will add that I'm pretty new to macros and VBA.

Screenshot example (I tried to use MrExcelHTMLMaker - I think it somewhat worked, the table didn't show too well):

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]Example Area[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]6/4/2019[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Proposal Number[/TD]
[TD="align: center"]Proposal Description[/TD]
[TD="align: center"]Proposal Location[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]92618[/TD]
[TD="align: center"]Example unit 1[/TD]
[TD="align: center"]Approved
18 APR 2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]93082[/TD]
[TD="align: center"]Example unit 2[/TD]
[TD="align: center"]Approved
06 JUN 2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]94489[/TD]
[TD="align: center"]Example unit 3[/TD]
[TD="align: center"]Approved
06 MAR 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]94540[/TD]
[TD="align: center"]Example unit 4[/TD]
[TD="align: center"]Implemented
14 MAR 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]94541[/TD]
[TD="align: center"]Example unit 5[/TD]
[TD="align: center"]Approved
14 MAR 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]94631[/TD]
[TD="align: center"]Example unit 6[/TD]
[TD="align: center"]Approved
24 APR 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]94663[/TD]
[TD="align: center"]Example unit 7[/TD]
[TD="align: center"]Approved
03 APR 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]94671[/TD]
[TD="align: center"]Example unit 8[/TD]
[TD="align: center"]Approved
29 MAY 2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]95134[/TD]
[TD="align: center"]Example unit 9[/TD]
[TD="align: center"]Under consideration[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Example Area​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

' Format_Table Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Range("A2:C11").Select
    Range("C11").Activate
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$C$11"), , xlYes).Name = _
        "Table1"
    Range("A2:C11").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight16"
    Columns("A:A").ColumnWidth = 12.6
    Columns("B:B").ColumnWidth = 53.73
    Columns("C:C").ColumnWidth = 15.2
    Columns("A:A").Select
    Range("A2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Columns("B:B").Select
    Range("B2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Columns("C:C").Select
    Range("C2").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A1:C2").Select
    Selection.Font.Bold = True
    Range("A2:C2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("A1:C2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
End Sub