Import data from another workbook reference to its sheet name,

[mrprofit]

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

[Excel Fox]

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 (http://www.excelfox.com/forum/f21/)

[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

[Ingolf]

This is crosspost...

Create Drop Down List for new opened sub worksheet Tabs (http://www.ozgrid.com/forum/showthread.php?t=187719)

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

[mrprofit]

saw it, thank you