Import data from another workbook reference to its sheet name,

[mrprofit]

OK, thank you, i will just stick to the first question, how to get these sheet names in to the dropdown list when any subWB is opened? the rest i can try to work it out

Sub UpdateValidationList()
     
    Dim wsArray As Variant
    Dim sWsList As String
    Dim x As Integer
     
    wsArray = AllWorkSheets()
     
     'Separate array of worksheet names into a string separated by commas.
    sWsList = Join(wsArray, ",")
     
     'Add sWsList string to data validation for "A1"
    With Sheets(2).Range("L1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=sWsList
    End With
End Sub
 
 
 '-----------------------------------------------------------------------------
 'Function to return an array of all worksheet names
 '-----------------------------------------------------------------------------
Public Function AllWorkSheets() As Variant
    Dim wsArray() As Variant
    Dim x As Integer
     
    ReDim wsArray(Sheets.Count - 1)
     
    For x = 0 To Sheets.Count - 1
        wsArray(x) = Sheets(x + 1).Name
    Next x
     
    AllWorkSheets = wsArray
     
End Function

how to modify this code so it can get opened subWB sheet names