Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am aware of doing cell formatting on multiple Tabs in a Excel workbook by pressing Ctrl and clicking on required Tabs (i.e by grouping multiple tabs).Do we have any similar way of adding data validation on multiple sheets or tabs?Please help so that we can save a lot of time...

share|improve this question

migrated from May 25 '11 at 13:35

This question came from our site for professional and enthusiast programmers.

This question should be moved to SuperUser. – Anonymous May 25 '11 at 12:33

I picked this up from another site - with a little fiddling it did the trick!

   With ws.Range("a3:a1000").Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Capabilities"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
share|improve this answer

Copy a cell containing the validation. Ctrl-Click the sheet tabs. Right-click the cell you want the validations applied to then do Paste Values > Validation.

If you're using a range of cells for your validation list, use a name for them. Highlight the range, go to Formulas then click Define Name. Enter a name for your list (i.e. "mylist") then click OK.

Select the cell to which you want to apply the validation. Open the Data Validation window. Set it to List and then enter the ff. into the formula box:

share|improve this answer

If you create a named List you can use it as the reference for multiple sheets. Copy the range with data validation as you wish. Select the multiple sheets as you are familiar with. Edit>Paste Special>Validation will do what you want.

share|improve this answer
same answer as Hidden Koala, I guess I was slower – datatoo May 25 '11 at 14:17

You must log in to answer this question.