Insert a Table Column and Fill It with Today's Date Using VBA in WPS Spreadsheets
Question details
The user wants to automate inserting a new first column into a named table, adding a header, and filling every existing table row with the current date using VBA.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with an existing named table and wants to add a date column without manually inserting and filling cells.
- Observed behavior
- The macro should create the new first column, name it, and populate all existing table rows with today's date.
Open the workbook in WPS Spreadsheets and note the exact worksheet name, table name, and desired column header. If the Developer tab is not visible, enable it via WPS Office Options before opening the VBA editor so you can access the macro module.
Use ListObjects and ListColumns to insert a table column and fill it with today's date
Use this VBA method when you want a reusable macro that inserts the column at the first position, names the header, and writes today's date to every existing table row.
The ListObject object represents the named table in WPS Spreadsheets. ListColumns.Add(1) inserts the new column at the first position, and the For Each ListRows loop writes the VBA Date value into the corresponding cell of the new column in each existing row.
In WPS Spreadsheets, press Alt+F11 or click Developer > Visual Basic to open the VBA editor.
In the VBA editor, click Insert > Module and paste the following macro, replacing Sheet1, TableName, and NewColumn with your actual names: Sub InsertColumnAndPopulateDate() Dim tbl As ListObject Dim newCol As ListColumn Dim row As ListRow Set tbl = ThisWorkbook.Sheets("Sheet1").ListObjects("TableName") Set newCol = tbl.ListColumns.Add(1) newCol.Name = "NewColumn" For Each row In tbl.ListRows row.Range(newCol.Index).Value = Date Next row End Sub
Change Sheet1 to the actual worksheet name, TableName to the exact named table, and NewColumn to the header you want.
Click Run > Run Sub/UserForm or press F5, then return to the worksheet and confirm that the new first column appears and each existing table row contains today's date.
Run the Same Table Date Macro Directly in WPS Spreadsheets
WPS Spreadsheets includes the VBA editor and ListObject/ListColumns objects, so you can create the named table column and fill today's date without leaving WPS Office.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains the target named table.
- 2. Open the VBA editor: Press Alt+F11, or enable the Developer tab and click Developer > Visual Basic.
- 3. Add and adjust the macro: Insert a module, paste the provided VBA code, and replace the worksheet, table, and column names with your own.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm, then switch back to WPS Spreadsheets to verify the new column and dates.
Frequently Asked Questions
Why does the macro fail with 'Subscript out of range'?
This usually means the worksheet or table name in the code does not exactly match the workbook. Check the sheet tab and the table name in the Name Manager or Table Tools, then update the ListObjects reference to match exactly.
Can I insert the new column in a different position instead of first?
Yes. In the code, change ListColumns.Add(1) to ListColumns.Add(Position), where Position is the 1-based column number where you want the new column to appear.
How do I keep the date from updating every time the workbook recalculates?
Use row.Range(newCol.Index).Value = Date instead of a formula. This writes the current date as a static value when the macro runs, so it does not recalculate later.
Will this VBA code work in WPS Spreadsheets with a Microsoft Excel file?
Yes. WPS Spreadsheets can open and run VBA macros in Excel .xlsx and .xlsm files. Make sure macros are enabled and save in a macro-enabled format if you add the code in WPS.
