Remove validation
 

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,D4:D200,E4:E200, G4:G200,L4:L200").Copy _
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

U can delete them after copy:

Sheets("Announcer").Activate
ActiveCell.SpecialCells(xlCellTypeAllValidation).C lear


"Curt" skrev:

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,D4:D200,E4:E200, G4:G200,L4:L200").Copy _
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

Or:

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


Mike F
"excelent" wrote in message
...
U can delete them after copy:

Sheets("Announcer").Activate
ActiveCell.SpecialCells(xlCellTypeAllValidation).C lear


"Curt" skrev:

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,D4:D200,E4:E200, G4:G200,L4:L200").Copy
_
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

OOPS. Had some extra stuff in that first line

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

"Mike Fogleman" wrote in message
m...
Or:

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


Mike F
"excelent" wrote in message
...
U can delete them after copy:

Sheets("Announcer").Activate
ActiveCell.SpecialCells(xlCellTypeAllValidation).C lear


"Curt" skrev:

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,D4:D200,E4:E200, G4:G200,L4:L200").Copy
_
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

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


"Curt" wrote in message
...
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,D4:D200,E4:E200, G4:G200,L4:L200").Copy _
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

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

"Mike Fogleman" wrote:

OOPS. Had some extra stuff in that first line

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

"Mike Fogleman" wrote in message
m...
Or:

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


Mike F
"excelent" wrote in message
...
U can delete them after copy:

Sheets("Announcer").Activate
ActiveCell.SpecialCells(xlCellTypeAllValidation).C lear


"Curt" skrev:

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,D4:D200,E4:E200, G4:G200,L4:L200").Copy
_
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

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



"Peter T" skrev:

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


"Curt" wrote in message
...
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,D4:D200,E4:E200, G4:G200,L4:L200").Copy _
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

Hi Excelent

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

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

--
Best regards
Joergen Bondesen


"excelent" wrote in message
...
cant get this 1 work
Worksheets("Announcer").Validation.Delete
but this 1 seems ok
Sheets("Announcer").Range("A2:E200").Validation.De lete



"Peter T" skrev:

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


"Curt" wrote in message
...
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,D4:D200,E4:E200, G4:G200,L4:L200").Copy
_
Destination:=Worksheets("Announcer").Range("A2")

Remove validation
 

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


"Peter T" <peter_t@discussions wrote in message
...
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


"Curt" wrote in message
...
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,D4:D200,E4:E200, G4:G200,L4:L200").Copy
_
Destination:=Worksheets("Announcer").Range("A2")