VBA & Macro Problems

How to Retain Data for Each Date in Excel Drop-Down Selections

Phi Hung VoPhi Hung Vo Oct 21, 2026 869 views

Question details

The user wants to switch between employee lists using a date drop-down menu in a schedule worksheet, but needs to preserve and selectively restore the data associated with each date rather than permanently clearing it.

How to Retain Data for Each Date in Excel Drop-Down Selections
Product
Excel
Device & OS
not provided
Scenario
Managing a dynamic schedule worksheet using date drop-downs to view and update different employee lists without losing previously entered data.
Observed behavior
When the date drop-down is changed, previous entries are permanently removed by a clear action in VBA, making it impossible to restore the original data for previously selected dates.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run properly, and map out a secondary sheet to act as your data storage before applying new code.

Solution 1Recommended

Implement a VBA Macro to Save and Load Date-Specific Data

Use a Worksheet_Change event to automatically save current entries to a hidden storage sheet and load the existing entries for the newly selected date.

Relying solely on the ClearContents method when a drop-down value changes will permanently delete your data. To retain records for each drop-down selection, you must build a mechanism that stores the data in a dedicated worksheet or table before clearing the cells, and then retrieves the relevant data for the new selection.

1
Set up a storage worksheet

Create a separate worksheet named 'DataStore' with dates in the first row and ample space below them to hold employee entries for each date.

2
Create the Data Validation drop-down

On your main schedule worksheet, select the cell for your date drop-down. Go to the Data tab, click Data Validation, choose 'List', and link it to your date range.

3
Open the VBA Editor

Press Alt + F11 to open the VBA Editor. In the Project Explorer, double-click the sheet containing your drop-down list to open its code module.

4
Add the save and load logic

Add a Private Sub Worksheet_Change(ByVal Target As Range) event. Write logic that first copies the active cell range to the 'DataStore' sheet under the previous date's column, clears the active input area, and then copies the stored data for the newly selected target date back into the input area.

Implement a VBA Macro to Save and Load Date-Specific Data
VBA Logic Complexity: Writing the exact save/load logic requires storing the 'previous selection' in a static variable or a hidden cell so the macro knows where to save the current data before pulling the new data.
Efficient Spreadsheet Management

Manage Advanced Spreadsheets and Macros with WPS Office

WPS Spreadsheet offers full support for Data Validation and VBA macros, allowing you to easily build automated schedules that retain data dynamically across drop-down selections.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open your schedule document.
  2. 2. Configure Data Validation: Navigate to the Data tab and click Data Validation to create your date drop-down lists.
  3. 3. Access the VBA Editor: Go to the Developer tab, click on 'Visual Basic', and insert your Worksheet_Change code to handle the data retention.
  4. 4. Save with Macros Enabled: Save the document as a Macro-Enabled Workbook (.xlsm) to ensure your automation triggers seamlessly every time.
Fully compatible with Microsoft Excel .xlsx and .xlsm formatsBuilt-in support for VBA and advanced macrosFree, lightweight, and user-friendly interfaceSeamless Data Validation tools for dynamic drop-downs
microsoft office alternative - wps office

Frequently Asked Questions

Why does ClearContents permanently delete my drop-down data?

The ClearContents VBA method strictly erases the values in the specified range. If you do not write additional code to copy or save those values to another hidden location beforehand, they cannot be recovered once the drop-down selection changes.

How do I enable the Developer tab to add VBA code?

In your spreadsheet application, go to Options > Customize Ribbon, and check the box next to 'Developer'. This will reveal the tab where you can access the Visual Basic Editor and manage macros.

Can I retain drop-down selection data without using Macros?

Yes, by using a separate master database sheet and utilizing dynamic array formulas like FILTER to display data based on the drop-down selection. However, with this method, you must enter new data directly into the database sheet rather than the display sheet.