×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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):

CODE -->

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

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

(OP)
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.

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

(OP)
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!

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

Quote:

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, www.Tek-Tips.com in the Visual Basic for Applications (Microsoft) forum, http://www.tek-tips.com/threadminder.cfm?pid=707

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources