I'm having a wee issue copying cells from one Worksheet to another in Excel VBA. The Source Sheet contains an individual client record stored in a single row. The Destination Sheet is a collection of such records, stacked row by row. It's important to keep them separate in for confidentiality.
I've built a macro to copy Source into the Destination Workbook, copy cells from Source, and paste them into the first empty row of the Destination Sheet. Since they both contain the same information, I've applied the same Data Validation to corresponding cells in each Sheet, mostly drop down lists.
However, the Source and Destination Sheets originate in different Workbooks. When I copy cells from Source to Destination, the Data Validation comes with. The cells pasted in Destination now refer to cells in Source to populate their drop down lists. The link usually breaks, breaking the drop down, and prompting the user each time Destination is opened.
What I'd like to do is copy the values in Source cells without copying the Data Validation. Like I said earlier, the Data Validation is the same in each Sheet (except that they refer to Sheets different Workbooks), so all the values should validate anyway.
Here is a sample of my copying code:
Sub copyNoValidation(wkbDestination as ThisWorkbook, oRange as Range, cellDest As Range) wkbDestination.Worksheets("Source").Range(oRange).Copy cellDest.PasteSpecial xlPasteValues End Sub
Thanks for your help!