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

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139
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:
Code:
[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!!!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
L

Legacy 98055

Guest
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

Board Regular
Joined
Jan 28, 2008
Messages
139
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... :biggrin:; 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
 
L

Legacy 98055

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

Legacy 98055

Guest
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

New Member
Joined
Apr 7, 2010
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,576
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top