Macro to add new column to a table, rename header, add formula to first blank cell, then refresh

[trevortownsend]

Hi all,

Obligatory "new to VBA"...

I'm trying to create a macro for my boss that would allow him to

1) Add a column to the far right of a table in any workbook
2) Name that column "Day of the week"
3) Find a column in the table with the header "Adj Order Date" and apply a formula in the column created in step 1 that changes the date from (ex: 2/19/2019) to (ex: Tuesday)
4) Refresh the workbook (so that the pivot linked to that table receives the new field titled "Day of the Week")

Rules:
Requirement 1 needs to be variable since he might be adding columns to the table at some point so it won't always be A:G for example.
Requirement 3 needs to be variable since "Adj Order Date" may not always be A1

• I'm guessing that formula would need to be =Text([Adj Order Date],"DDDD")
• I'm also guessing that this formula should autofill because the newly created column will be part of the table

Current Progress:
I'm pretty much stuck after step 1. Any help would be greatly GREATLY appreciated!

'Step 1​

Sub Test()​

Dim LastCol As String​

​

LastCol = ActiveSheet.ListObjects("Table1").Range.Columns.Count​

​

Columns(LastCol - 0).EntireColumn.Insert​

​

ActiveSheet.Columns(LastCol + 1).Cut​

Columns(LastCol - 0).EntireColumn.Insert

'Step 4
ActiveWorkbook.RefreshAll​

​

End Sub​

Example header and first row from the table1 referenced above:

[TABLE="width: 964"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Adj Order Date[/TD]
[TD]Adj Fiscal Quarter[/TD]
[TD]Fiscal Quarter[/TD]
[TD]Order Number[/TD]
[TD]End User Organization[/TD]
[TD]$ Constant Currency[/TD]
[TD]Day of the Week[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]2/11/2019[/TD]
[TD]19Q4[/TD]
[TD]20Q1[/TD]
[TD]1234567[/TD]
[TD]Made up Organization[/TD]
[TD]$100,000,000[/TD]
[TD]Tuesday[/TD]
[/TR]
</tbody>[/TABLE]


Thank you,

 

[igold]

Hi trevortownsend,

Welcome to the MrExcel Forum.

See if this gets you any closer to where you need to be. My only requirement is that the current activecell is anywhere in the table you want to act on.

Sub TblCol()


    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim tbl As ListObject
    Dim tblct As Long, t As Long, i As Long, colct As Long, hr As Long
    Dim tblname As String
    
    tblct = ws.ListObjects.Count
        For i = 1 To tblct
            If ws.ListObjects(i).Active Then
                tblname = ws.ListObjects(i).Name
                Exit For
            End If
        Next
        
    On Error GoTo NoTableSelected
    Set tbl = ws.ListObjects(tblname)
    On Error GoTo 0
    tbl.ListColumns.Add
    colct = tbl.ListColumns.Count
    tbl.ListColumns(colct).Name = "Day of the Week"
    tbl.DataBodyRange(1, colct).Select
    ActiveCell.Formula = "=[@[Adj Order Date]]"
    tbl.ListColumns(colct).DataBodyRange.NumberFormat = "dddd"


    Exit Sub


NoTableSelected:
  MsgBox "There is no Table currently selected!", vbCritical
   
End Sub

I hope this helps.

 

[Norie]

If this is to show the day of the week in the pivot table why not add the 'Adj Order Date' column to the pivot table again, format it as 'dddd' and change the field name.

 

[igold]

Errant post...

 

[trevortownsend]

@Norie,

That was my first try without VBA since it's simple solution, however in my pivot it kept showing as every repeating days. For example, in my data set I have ~52 sets of workweeks, so it was returning 52 mondays, 52 tuesdays, etc. with the method above. Not sure if I was doing something wrong though. Thanks for the input!

 

[trevortownsend]

@igold

Thank you! I edited the last few lines to look like the below only so that I could ensure only the actual word was returned instead of the formatted date since I was having trouble with the pivot returning every repeat day of the week for each actual date. For For example, in my data set I have ~52 sets of workweeks, so it was returning 52 mondays, 52 tuesdays, etc. with the method you provided. My next work will be to understand how to do this without having to click into the table to make it active, but that's something I can learn . Thanks for your super quick help, much appreciated!

ActiveCell.Formula = "=Text([Adj Order Date],""DDDD"")"
tbl.ListColumns(colct).DataBodyRange.NumberFormat = "General"
ActiveWorkbook.RefreshAll

 

[igold]

Happy to help, thanks for the feedback!

 

[igold]

I find this to be a good resource when working with tables...

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables