Is there any way to copy cell data validation without using copy & pastespecial commands?

[Agent666]

Hi

As per title, I was wondering if cell data validation settings be copied without a range().copy & range().pastespecial operation in Excel.

Its in an effort to solve a dreaded screen flicker that occurs when forced to copy/paste in this manner (yes I have Application.ScreenUpdating = false). Data validation on sheet(2) is applied to a similar set of cells in sheet(3)

An internet search doesn't turn anything up, no way of reading all the validation settings and then applying the same settings to destination cells, really after something like you can do with number formats using range().numberformat ideally, does anything exist?



Thanks in advance

 

[zelgar]

You can use the Copy Destination option. Here's some code from one of my spreadsheets (Note: the source and destination are on 2 different sheets):

Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 21)).Copy _
    Destination:=Sheet7.Cells(1, 1)

 

[Agent666]

Hi thanks for the reply, Iv'e realised the question wasn't asked particularly well.

I just want to copy the validation, value and number format. I can handle the number format and value without selecting cells and pasting. But not the data validation, so if anyone knows a way to do it without selecting a selection and pastespecial operation please let me know.

 

[IDS]

This thread:

http://www.ozgrid.com/forum/showthread.php?t=60096

suggests that using copy and paste-special in VBA is the only way to do it.

I only did a brief search, but I didn't find anything to suggest otherwise.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Agent666]

the function at the end was exactly the lead I needed to read and apply only the data validation between source and target ranges. Thanks worked a treat, still have the flashing due to some other dodgy code... one step at a time I guess!

 

[SkipVought]

still have the flashing due to some other dodgy code

Check out the ScreenUpdating property.

Avoid using Activate and Select methods. Rather reference objects directly, like worksheets and ranges.

For tips from computer professionals, consider posting in Eng-Tips sister site,

http://www.Tek-Tips.com

in the Visual Basic for Applications (Microsoft) forum,

http://www.tek-tips.com/threadminder.cfm?pid=707

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Agent666]

I did figure out what it was in the end, seemed to depend on which subroutine I called the application.screenupdating = true/false from.

Had some code where it would set it to true and then in a further called sub would set it back to false, before returning back to the original sub. Wasn't turning it on/off more than once or anything. Funny thing was in other code doing the same thing had no flashing, so who knows!