Fix Excel Data Validation Disappearing After Copying a Worksheet in WPS Office Spreadsheets
Question details
The user needs to determine why Excel data validation rules disappear when a worksheet is moved or copied between workbooks and how to keep those validation rules intact.
- Product
- Microsoft 365 Personal and WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Copying or moving a worksheet between workbooks while retaining data validation rules.
- Observed behavior
- Data validation rules disappear from the original or copied sheet after moving or copying the worksheet.
Open your workbook in WPS Office Spreadsheets and check whether each data validation rule references a range on the same sheet or on another worksheet. Cross-sheet and external workbook references are the most common cause of missing validation after copying.
Copy the Source Range and Data Validation Rules Together in WPS Spreadsheets
Use this method when you want the data validation rules to move with the copied data instead of recreating them manually.
Excel sometimes drops validation rules when copying an entire sheet because the validation references a range on another sheet or workbook. In WPS Office Spreadsheets, copying the used range together with its validation settings helps preserve the rules.
Launch WPS Office and open the workbook that contains the worksheet with data validation rules.
Click the source worksheet tab, then select the exact range that contains the cells with data validation, including any helper cells referenced by the validation rule.
Press Ctrl+C to copy the selected range.
Go to the destination worksheet, click Home > Paste > Paste Special, or right-click the target cell and choose Paste Special.
In the Paste Special dialog, choose Validation and click OK to paste only the data validation rules. If you also need the cell contents, paste the values normally or use All to copy values and validation together.
Select the pasted range, click Data > Data Validation, and confirm that the criteria, input message, and error alert are present.
Recreate the Data Validation Rule After Copying the Worksheet
Use this method when validation rules were already lost or when the rule references an external workbook or another worksheet.
Keep Data Validation Rules Intact When Copying Worksheets in WPS Office Spreadsheets
WPS Office Spreadsheets lets you copy a worksheet and manage data validation rules directly in a familiar spreadsheet interface. You can paste validation settings with the data, recreate broken cross-sheet references, and save the workbook as a standard Microsoft Excel file.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the .xlsx workbook that contains data validation.
- 2. Copy the worksheet with validation: Right-click the sheet tab, select Move or Copy, choose the destination workbook, check Create a copy, and click OK.
- 3. Create or adjust validation: Select the copied cells, go to Data > Data Validation, and point each rule to a range on the current sheet or recreate the rule if it was lost.
- 4. Save as an Excel format: Click Menu > Save As, choose Excel Workbook (.xlsx) or Excel 97-2003 (.xls) to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does data validation disappear when I copy an Excel worksheet?
The most common cause is that the data validation rule references a range on another worksheet or workbook. When the sheet is moved or copied, that cross-sheet or external reference can break, so Excel or WPS Spreadsheets may not carry the rule to the new location.
How do I copy data validation rules without losing them?
In WPS Spreadsheets, select the source range, press Ctrl+C, then use Home > Paste > Paste Special and choose Validation. This pastes only the validation settings or you can use All to copy values and validation together.
Can WPS Office open and edit an Excel workbook with data validation?
Yes. Open the workbook in WPS Office Spreadsheets, select the validated cells, and go to Data > Data Validation to view or edit the rule settings. You can save the file as .xlsx or .xls for Microsoft Excel compatibility.
Does WPS Spreadsheets keep data validation when copying sheets between workbooks?
It generally keeps validation rules when the referenced ranges remain valid. If a rule points to another sheet or workbook, the reference may break, and you should recreate the rule in the destination workbook.
