| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove blank cells from data validation drop down box | Excel Worksheet Functions | |||
| How do I remove data validation enteries in Excel 97? | Excel Discussion (Misc queries) | |||
| Remove Duplication from Validation List? | Excel Discussion (Misc queries) | |||
| Remove duplication from validation list? | Excel Worksheet Functions | |||
| Remove Data validation Input messages | Excel Programming | |||
Linear Mode
