Using "Pull" UDF 1,200 times withing a sheet on a 12 worksheet workbook

[ron2k]

Season's greetings to all

I have a workbook with 12 identical worksheets, where on each worksheet there are around 1,200 cells that could have required the use of INDIRECT, but the data needs to be pulled from closed workbooks so, I have considered the UDF "Pull" from this thread http://www.mrexcel.com/forum/showpost.php?p=2131711&postcount=5

Now can you guys advise me if this advisable, because while I was testing, I tried it on one cell and there was a delay of like 1/2 sec when updating that one cell, so can you tell me if it would be a bad idea to use on all 12 identical worksheets that basically will all update at the same time depending on month selection to be done from a drop down list on B3 on any of the worksheets?

On a similar note, a member from another thread has provided me with this code so that ll worksheets update to the selection done on B3 on any worksheet:

[FONT=Arial][SIZE=2][COLOR=#000000]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet
On Error GoTo ExitPoint
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> Sh.Name Then ws.Range("B3").Value = Target.Value
Next ws
ExitPoint:
Application.EnableEvents = True
End Sub</pre>[/COLOR][/SIZE][/FONT]

I have requested help on this thread http://www.excelforum.com/excel-pro...ll-worksheets-simultaneously.html#post2209416

About the issue that given that I have that UDF it not working too well. If I remove the "pull" formula from all cells which I used to test. Then that code works. Any ideas on this? I can't even inset any line in the immediate window because of that UDF, I believe...

Thank you guys!!!

 

[Legacy 98055]

I would not. At least not before considering naming my source ranges and querying by way of ADO or MSQuery. It really depends on how well your source data is arranged...

 

[ron2k]

I would not. At least not before considering naming my source ranges and querying by way of ADO or MSQuery. It really depends on how well your source data is arranged...

Here are a sample of the worksheet which has that combination of SUMPRODUCT, and I also attach 2010 source file, I have five source data files, so that's why I was planning on using "Pull". If you notice on the results file (where I have sumproduct) I have one sheet for each sheet in the source file, and named identically. So by using "pull" I can have the formula to extract the tab name, and then on each column I can just add the years headings and the lookup ranges, and that formula will apply for all years and for all branches (tabs or sheets).

Any other suggestion. If you can help me with a MSQuery or, I don't know what is ADO, but if it works for what I want it. By all means recommend what you think its fit. I'll be thankful till thy kingdom come... ; as I usually am from this forum...

Thanks.

EDIT: I couldn't upload them here but if you are a member of this forum you can download them from here: http://www.excelforum.com/2209416-post10.html

 

[Legacy 98055]

You are welcomw to email them too me if you wish. See my profile and contact info.

 

[Legacy 98055]

Hi Ron. Before going any further. Is there any reason that we cannot simply open the linksources automatically, update the links, and then close. This could be done behind the scenes. The pull function literally creates another instance of Excel and then adds a workbook for each call. This can be sped up greatly by creating the instance outside of the function as a module level variable and then simply reusing it. Even so, the pull function seems to be ok for grabbing a value or two occasionally but definitely not 1200 calls. What about it???

 

[1SLwLS1]

Hi Ron. Before going any further. Is there any reason that we cannot simply open the linksources automatically, update the links, and then close. This could be done behind the scenes. The pull function literally creates another instance of Excel and then adds a workbook for each call. This can be sped up greatly by creating the instance outside of the function as a module level variable and then simply reusing it. Even so, the pull function seems to be ok for grabbing a value or two occasionally but definitely not 1200 calls. What about it???

Sorry to bump this, but I ran across this doing the exact same thing with the pull function. Care to elaborate on how exactly to speed the process up? I'm a newb at VBA