Remove validation

[Guest]

My main worksheet has a column with a pulldown for valid entries.
Is there a way to remove this valadation when copying the data in this
column to another sheet.
I have the same problem in another column
The copies are to be used for mail merge if you enter a cell the validation
comes up not needed now.
here is code I use to copy
Thanks

Worksheets("Data").Range("A4:A200,D4200,E4:E200,G4:G200,L4:L200").Copy _
Destination:=Worksheets("Announcer").Range("A2")

 

[Guest]

U can delete them after copy:

Sheets("Announcer").Activate
ActiveCell.SpecialCells(xlCellTypeAllValidation).Clear


"Curt" skrev:

 

[Mike Fogleman]

Or:

Range("D4").CopyWorksheets("Data").Range("A4:A200,D4200,E4:E200,G4:G200,L4:L200").Copy
Worksheets("Announcer").Range("A2").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False


Mike F

 

[Mike Fogleman]

OOPS. Had some extra stuff in that first line

Worksheets("Data").Range("A4:A200,D4200,E4:E200,G4:G200,L4:L200").Copy
Worksheets("Announcer").Range("A2").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

 

[Peter T]

Unless you particularly need to paste formats go with Mike Fogleman's paste
values (or paste formulas) solution. Otherwise you could delete validation,
if you don't need any DV on the destination sheet at all simply

Worksheets("Announcer").Validation.Delete

FWIW, your multi-area copy/paste only works because all areas are same size,
otherwise would need to do each area separately.

Regards,
Peter T

 

[Guest]

You make it seam so simple
Thanks
do have another to stop it is some code that does a check to see if row data
is complete. Will deal with that one when this one done.
Thanks Again

 

[Guest]

cant get this 1 work
Worksheets("Announcer").Validation.Delete
but this 1 seems ok
Sheets("Announcer").Range("A2:E200").Validation.Delete



"Peter T" skrev:

 

[Joergen Bondesen]

Hi Excelent

If you want to delete Datavalidation on the whole sheet, do below please:

Worksheets("Announcer").Cells.Validation.Delete

 

[Peter T]

As pointed out by excellent and Joergen, .Validation applies to a range
object and not the worksheet object as in my mistaken example

Regards,
Peter T