CODEWITHSUNDEEP
excelvalidationvba
john
john

Reputation: 2662

vba for excel - create a validation list based on the data from another sheet in the same book

I have an urgent need for VBA on excel:

On sheet 1, i have

A         B                  C
-----------------------------------
        DATASET 1           variable1
                            variable2
                            variable3
        DATASET 2           variable4
                            variable5
                            variable6

        .......

on next sheet (sheet 2, for example)

i have

    D                   F
    -----------------------------------
    DATASET 1           variable1
                        variable2
                        variable3
                        ...
                        variable100
    DATASET 2           variable4
                        variable5
                        variable6
                        variable200

        .......

how can I write a VBA macro to create validation list for sheet 1 data entry. When I click a cell to enter variable 1 for Dataset 1, it will display a list that has been pulled from sheet 2 filtered by dataset name (e.g. dataset 1)?

thanks.

Upvotes: 0

Views: 2002

Answers (1)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

Assuming the layout shown is accurate and column A has empty cells between each potential Named Range group, this macro will create a named range from each column A value using the range of cells in column B:

Option Explicit

Sub AddNames()
Dim RNG As Range, LR As Long, Nm As Long

With ActiveSheet
    LR = .Range("B" & .Rows.Count).End(xlUp).Row
    Set RNG = Range("A:A").SpecialCells(xlConstants)

    For Nm = 1 To RNG.Areas.Count
        If Nm < RNG.Areas.Count Then
            ActiveWorkbook.Names.Add Name:=Replace(RNG.Areas(Nm).Cells(1).Value, " ", ""), _
                RefersToR1C1:="='" & .Name & "'!R" & RNG.Areas(Nm).Cells(1).Row & _
                    "C2:R" & RNG.Areas(Nm + 1).Cells(1).Row - 1 & "C2"
        Else
            ActiveWorkbook.Names.Add Name:=Replace(RNG.Areas(Nm).Cells(1).Value, " ", ""), _
                RefersToR1C1:="='" & .Name & "'!R" & RNG.Areas(Nm).Cells(1).Row & _
                    "C2:R" & LR & "C2"
        End If
    Next Nm
End With

End Sub

Upvotes: 1

Related Questions