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

jefffyluber

New Member
Joined
Jun 4, 2019
Messages
1
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):
ABCD
1Example Area
26/4/2019
3Proposal NumberProposal DescriptionProposal Location
492618Example unit 1Approved
18 APR 2018
593082Example unit 2Approved
06 JUN 2018
694489Example unit 3Approved
06 MAR 2019
794540Example unit 4Implemented
14 MAR 2019
894541Example unit 5Approved
14 MAR 2019
994631Example unit 6Approved
24 APR 2019
1094663Example unit 7Approved
03 APR 2019
1194671Example unit 8Approved
29 MAY 2019
1295134Example unit 9Under consideration
13
14

<tbody>
</tbody>
Example Area

Worksheet Formulas
CellFormula
A2=TODAY()

<tbody>
</tbody>

<tbody>
</tbody>




Rich (BB code):
Rich (BB code):
' 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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,560
Messages
6,114,309
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