trevortownsend
New Member
- Joined
- Feb 11, 2019
- Messages
- 4
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
Current Progress:
I'm pretty much stuck after step 1. Any help would be greatly GREATLY appreciated!
Example header and first row from the table1 referenced above:
<tbody>
</tbody>
Thank you,
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
'Step 4
ActiveWorkbook.RefreshAll
End Sub
Example header and first row from the table1 referenced above:
A | B | C | D | E | F | G | |
1 | Adj Order Date | Adj Fiscal Quarter | Fiscal Quarter | Order Number | End User Organization | $ Constant Currency | Day of the Week |
2 | 2/11/2019 | 19Q4 | 20Q1 | 1234567 | Made up Organization | $100,000,000 | Tuesday |
<tbody>
</tbody>
Thank you,