Import data from another workbook reference to its sheet name,

I have a MainWB, on sheet2 L1 is the dropdown list to list names of sheetnames of any subWB that opened, how to get these sheet names in to the dropdown list when any subWB is opened? i have many subWBs with different sheet names.

and then i would like when the sheetname is selected from the dropdown list, it will extract the last no. of rows data (eg. 500 rows) from that sheet to the MainWB Sheet1 A2 with reference to the last row "time"

the data in the subWB is

Date value value value value value time
.....
.....
.....
.....
.....
Apr/1/2014 2000 100 120 100 110 11：30
Apr/2/2014 1000 120 130 110 110 11：40
Apr/3/2014 2000 100 120 100 110 11：50
Apr/4/2014 1200 120 130 110 110 12：00
Apr/5/2014 1300 100 120 100 110 12：10
Apr/6/2014 1400 120 130 110 110 12：20
Apr/7/2014 1500 100 120 100 110 12：30
Apr/8/2014 1800 120 130 110 110 12：40

if any sheet that has more time than the average last time, will get the minimal time, thats to say all data extracted the last time must be the same, any sheet with time greater than the lesser one, the rows will not be copied. all the data extract from the above sheet last time should be April/8/2014 12:40, that is the common last date

I hope i describe it clearly, thanks for any help

mrprofit, this looks more like a request for a project help, then just a specific help. I would suggest you opt to offer a little compensation to a developer who can take this up. Click here to post a query to Hire a Developer

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

Code:

---

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

This is crosspost...

Create Drop Down List for new opened sub worksheet Tabs

where you received the response but did not condescend to answer ....

saw it, thank you