Validate Required Excel Fields and Preserve Date Formats with VBA
Question details
Validate required input cells before copying their values to another worksheet, while preserving date formats.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA submission macro copies user-entered data from an input worksheet to a destination worksheet.
- Observed behavior
- Required fields need to be checked before submission, and copied dates can appear as serial numbers when their number formats are not preserved.
Identify the required input cells, source range, and destination worksheet in your workbook. Save the file as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only if you trust the workbook.
Validate Required Cells and Copy Values with Number Formats
Use this VBA approach to stop submission at an empty required field and preserve date formatting in the destination.
The example checks cells B2:B4 on a sheet named Input, then copies those cells to B2:B4 on a sheet named Records. Adjust the sheet names, required cells, and destination to match your workbook.
PasteSpecial with xlPasteValuesAndNumberFormats copies the displayed values and number formats, not the formulas or all other cell formatting.
In Excel, open the workbook and choose Developer > Visual Basic. If the Developer tab is hidden, enable it through Excel's ribbon customization settings.
In the VBA editor, choose Insert > Module and paste this example: Sub SubmitData() Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim cell As Range Set sourceSheet = ThisWorkbook.Worksheets("Input") Set destinationSheet = ThisWorkbook.Worksheets("Records") For Each cell In sourceSheet.Range("B2:B4") If IsError(cell.Value2) Then MsgBox "Please correct the error in " & cell.Address(False, False) & "." sourceSheet.Activate cell.Select Exit Sub End If If Len(Trim$(CStr(cell.Value2))) = 0 Then MsgBox "Please complete required field " & cell.Address(False, False) & "." sourceSheet.Activate cell.Select Exit Sub End If Next cell sourceSheet.Range("B2:B4").Copy destinationSheet.Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats Application.CutCopyMode = False MsgBox "Data submitted." End Sub
Change the worksheet names and ranges in the module to match your form. Run the macro from Developer > Macros with a required field empty, then fill all fields and run it again to check the destination dates.

Work with Excel Spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Open Excel files in WPS Spreadsheets for an easy transition, but use Excel to implement and test the VBA macro described above.

Frequently Asked Questions
Why does a copied date appear as a number in Excel?
Excel stores dates as serial numbers. If the destination uses a General or numeric format, the serial number may appear instead of a date. Copy the source with xlPasteValuesAndNumberFormats or apply a date format to the destination cells.
How does the macro identify an empty required field?
The example loops through Input!B2:B4 and checks each cell with Len(Trim$(CStr(cell.Value2))). If the result is zero, it displays a message, selects that cell, and exits the procedure.
Will the macro copy formulas to the destination?
No. PasteSpecial with xlPasteValuesAndNumberFormats copies the formula results and number formats, not the formulas themselves.
How do I keep each submission instead of overwriting the previous one?
Replace the fixed Records!B2 destination with logic that finds the next available location on the Records sheet. Make sure the destination layout matches the shape of the source range before pasting.




