Get Selected Value from Data Validation List

If you copy and then Pastespecial xlPasteValues, you will get the data only. The validations, formats, formulas, etc. in the sheet being copied into should not change.

If you need more help, include a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks.

So I've been using a .Copy command. I'll look into a xlPasteValues, that might solve the problem.
If it doesn't, I can attach the entire file. There's nothing in there that's private/secret/confidential.

Merged cells are a bad idea. It's OK to use merged cells as titles but not as column headers or to hold data. There are some exceptions, but none of them involve copy / paste.

First idea: Use Center Across Selection instead of merged cells. The results look the same. The unfortunate thing is Center Across Selection is buried in the Alignment Tab of Format Cells whereas Merge and Center is staring you in the face on the Home Ribbon. Microsoft should put Center Across Selection in the number one spot on that menu.

Second idea: merge the cells in what you are copying the same way they are merged to what you are copying to.

Third idea: un-merge all cells and then re-merge what you have to.

i think you are beginning to see why people who played with Excel for a while avoid merged cells when they can.

Don't use Merge Across.

Unmerge the cells. Highlight the range you want it to center on.

Right click it and select Format Cells to bring up the Format cells dialog box.

Click on the alignment tab. Under Horizontal, use the drop-down to select Center Across Selection.