Hi Yogesh, I only have one other idea to stay with your worksheet functions. All your Countifs functions contain whole column references.
eg: =COUNTIFS(DB_Sheet!$D:$D,$A16,DB_Sheet!$E:$E,K$2,DB_Sheet!$B:$B,$A$74) Since none of your DB_Sheet data goes beyond row 288325, you could change EVERY formula: eg: =COUNTIFS(DB_Sheet!$D1:$D300000,$A16,DB_Sheet!$E1:$E300000,K$2,DB_Sheet!$B1:$B300000,$A$74) This MAY reduce the time significantly. Regards - Dave. Date: Sun, 24 Apr 2011 09:03:05 +0530 Subject: Re: $$Excel-Macros$$ Help me in this From: yashr...@gmail.com To: excel-macros@googlegroups.com Thanks Dave, for your suggestion. but problem is that i want to update all sheets on daily basis. If i'll do this, it also be take same time. If you have any other idea please share with me. I am also working on it if i will get any solution then i will tell you. Thanks & Regards Yogesh Gohil On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack <davebonall...@hotmail.com> wrote: Hi Yogesh, At first glance, I'd say that you just have too many formulas in the workbook, and that most of the calculations should probably be done by VBA rather than worksheet functions. There appear to be some UDF's, but the VBA is protected, so I can't examine them, or make any decision on how to speed up the process. I think I would populate each sheet separately with VBA, and only update each sheet when it is activated, using a Worksheet_Activate event. However, it may be worth trying the following before going wholesale into VBA. Each sheet has an EnableCalculation property. In VBA, set each sheet to False when the workbook opens. The code would go into the ThisWorkbook window, as a Workbook_Open event. Something like: For each s in Worksheets s.EnableCalculation = False Next s Then, in the sheet window for each sheet, as a Worksheet_Activate event, something like: Activesheet.EnableCalculation = True Activatesheet.Calculate And as a Worksheet_Deactivate event, something like: Activesheet.EnableCalculation = False This way, a worksheet is only updated when needed. It still may take too long, but it's simple to do, and worth a try. But I still think VBA could do each sheet in seconds, not hours. Regards - Dave. Date: Sat, 23 Apr 2011 09:07:55 +0530 Subject: $$Excel-Macros$$ Help me in this From: yashr...@gmail.com To: excel-macros@googlegroups.com Hi Experts, Actually, this waterfall made by me but its take 3hour to complete. Please help me out to make easy and time reducer format. This format have Excel function and VBA coding. For the attachment you can click on http://www.megaupload.com/?d=8KTIVGZX Thanks & Regards Yogesh Gohil -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
