Excel 2007 copy validation down the column for 30 columns 1

[BxWill]

Have a 5000+ row 30 column Excel 2007 worksheet that I manually add over 75 records to each week. Each column contain unique list validations.

Current Procedure: After copy/pasting the 75 records each week, I manually copy/paste the validation down each column for each of the newly added records that is quite time consuming - especially if there is pre-existing data in a particular cell within a column.

Desired Procedure: Upon manually pasting the 75 records, automatically copy the validation in the first cell of the column down to the last row of data (for each of the 30 columns). Note, data will always exist in column A.

Is this possible?

Currently experimenting with the following, but would appreciate any insight considering that I have spent over an hour trying to resolve.

Sub CopyValidation()
'Copies cell validation from cell B1 to B2
Dim ToCell As Validation
Set ToCell = [B2].Validation
With [B1].Validation
ToCell.Add _
Type:=xlValidateList, _
AlertStyle:=.AlertStyle, _
Operator:=.Operator, _
Formula1:=.Formula1

ToCell.IgnoreBlank = .IgnoreBlank
ToCell.InCellDropdown = True
ToCell.InputMessage = .InputMessage
ToCell.ErrorMessage = .ErrorMessage
ToCell.ShowInput = .ShowInput
ToCell.ShowError = .ShowError
End With
End Sub

 

[ease20022002]

Hi, I might be a little late but I would try copying range("A1:AF1"), e.g., Range("A1:AF1").Copy, or [A1:AF1].Copy.

Personally, I don't use the brackets b.c I was running an app the other day that I had used with brackets and the cod just stopped on a line that only had brackets and copy, or paste, I don't remember correctly. That one runin had me change my code. I don't trust it.

Anyway, then you need to find a way to copy that "row" range down to the last row in your list.

If you have a column that does not have any cells that are empty, then you can use that one column that is contiguous and do, e.g., Range("[Column Letter]1").End(xlDown) to find the last row and copy all of the ranges down...Or you can just copy the validation down an arbitrary number like 20k rows, etc., until you need to extend it.

You could also use the CountA function...etc...

Look into those things.

GL

 

[SkipVought]

No need for any code.

Use Data > Create list... to create a, uh, er, LIST.

Then each time you PASTE new values in the LEFT side of the LIST, the Data Validation columns will propogate with the added data.

VOLA, y'all!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]