Paste Special in Excel

[muimui]

Is there anyway that I copy validation from one worksheet and paste it on another sheet?


Is it possible to copy everything on one sheet and then paste only the values, formats, validations, conditional formating...etc but NOT the command button and the code behind the command button to a new sheet?

Thanks so much

 

[avrcan]

1. You can copy validation (Copy-Paste/PasteSpecial(constant xlPasteAll)) but you must be careful with data validate from list (source will refer to range on the worksheet to wich you pasted data).

2. It's possible to paste only values, formats, etc, as follow:

Range("B24&quot.Copy ' copy data
Range("B6&quot.PasteSpecial (xlPasteValues) ' paste only values
Range("B6&quot.PasteSpecial (xlPasteFormats)' paste only format
Range("B6&quot.PasteSpecial (xlPasteComments)' paste only comm.
Range("B6&quot.PasteSpecial (xlPasteAll) ' paste all

Refere to help topic: PasteSpecial Method

 

[muimui]

I don't want the paste all because I've a button (with VBA code) behind in in the original page and I don't need the button on the new page.
It didn't work when I tried
Activesheet.CommandButton(CommandButton1).select
Selection.delete

 

[avrcan]

If you want to delete button, try something like this:

Sub Delete_Buttons_1()
Dim sh As Shape

For Each sh In ActiveSheet.Shapes
If Left(sh.Name, 13) = "CommandButton" Then sh.Delete
Next
End Sub

Sub Delete_Buttons_2()
Dim sh As Shape

For Each sh In ActiveSheet.Shapes
If sh.Name Like "CommandButton*" Then sh.Delete
Next
End Sub