CODEWITHSUNDEEP
vbaexcel
David S.
David S.

Reputation: 119

Copy data validations only from a single row from one table to all rows of another table

I'd like to copy only the data validations from a table called Table1_1 on the worksheet TEMPLATE (Maint.) to a table on another worksheet called TEMPLATE. I have looked at the available topic but none come quite close to what I am looking for.

One of the problems are the table on either of these sheets may end up shifting so when I build this macro I will need to take that into account.

So far what I have is:

  1. Copy the first (and only row) of Table1_1 on worksheet TEMPLATE (Maint.).
  2. Look at cell A3 on worksheet TEMPLATE and get the table it belongs to.
  3. Find the first row of the table found in step 2.
  4. Paste data validations of all columns in the table on the first row.
  5. Repeat steps 3 and 4 for all rows of the table.

The code I have so far:

Dim TotalSheets As Integer
Dim p As Integer
Dim iAnswer As VbMsgBoxResult

' This Dim is supposed to be to add worksheets, for the process _
' of copying the data validations to the new sheets, to a skip _
' list. An array perhaps? Skip any sheets listed in this array?
Dim DNCToShts As ?

' The cell to get the table that it is apart of for copying from _
' the other worksheet, "TEMPLATE (Maint.)"
Dim GetCellsTable_Copy As String
' The cell to get the table that it is apart of for pasting onto _
' the other worksheets.
Dim GetCellsTable_Paste As String

' This is the cell to reference on "TEMPLATE (Maint.)" worksheet _
' to get the table name of, this will always be "Table1_1"
GetCellsTable_Copy = "A3"
' This is the cell to reference on each sheet to get the table name.
GetCellsTable_Paste = "A3"

With Aplication
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

iAnswer = MsgBox("You are about to copy data validations! Do you _
want to proceed?", vbOKCancel + vbExclamation _
+ vbDefaultButton2 + vbMsgBoxSetForeground + vbApplicationModal, _
"Copying Data Valadations")

' Instead of copying the whole table I just need to copy the first row _
' of data, intending to copy just the data validations portion.
Range("Table1_1").Copy

If iAnswer = vbYes Then
    p = 1 To Sheets.Count
        If UCase$(Sheets(p).Name) <> DNCToShts
        StoreTableName = Range(GetCellsTable_Paste).ListObject.Name

I have created a diagram showing what I am aiming to accomplish with each of my Excel VBA modules. Keep in mind this may not include all details and I am working on Part 1 only.: Diagram showing the sequence of sub usage

Upvotes: 0

Views: 338

Answers (1)

MikeC
MikeC

Reputation: 958

The Excel VBA online help has everything needed for you to have done this. Just the help page for Validation object members should be sufficient.

The following routine will copy a validation from one cell to another. You should be able to call it in a double-loop (for target rows and columns). Once you're done testing it out, this should probably be a Private function

Sub CopyValidation(ByRef rngSourceCell As Range, ByRef rngTargetCell As Range)
    With rngTargetCell.Validation
        .Delete
        .Add Type:=rngSourceCell.Validation.Type, _
            AlertStyle:=rngSourceCell.Validation.AlertStyle, _
            Operator:=rngSourceCell.Validation.Operator, Formula1:=rngSourceCell.Validation.Formula1, Formula2:=rngSourceCell.Validation.Formula2
        .ErrorMessage = rngSourceCell.Validation.ErrorMessage
        .ErrorTitle = rngSourceCell.Validation.ErrorTitle
        .IgnoreBlank = rngSourceCell.Validation.IgnoreBlank
        .IMEMode = rngSourceCell.Validation.IMEMode
        .InCellDropdown = rngSourceCell.Validation.InCellDropdown
        .InputMessage = rngSourceCell.Validation.InputMessage
        .InputTitle = rngSourceCell.Validation.InputTitle
        .ShowError = rngSourceCell.Validation.ShowError
        .ShowInput = rngSourceCell.Validation.ShowInput
    End With
End Sub

Upvotes: 1

Related Questions