Best way to pull data from several different sheets into a summary sheet

[Greg]

All,

I have a VBA-formula hybrid workbook that is used to track employees'
vacation time. It is calendar-style, with the days of the month 1-3x
running from left to right, months running vertically. For a day off,
you enter in the code for the type of vacation and the number of hours
in the cell below. Each employee has their own sheet and each
department has their own workbook.

I was recently asked to add a 'team calendar' sheet that would
consolidate all employees' time off onto one page so the manager could
see if there were vacation conflicts.

(VBA-approach) I first tried to accomplish this by having a For/Next
loop circle through all appropriate cells per employee sheet and
export to the Team Calendar tab. This, unfortunately, took a lot of
time to do and was not very efficient. For reference, 5 employees
could take up to 15-20 seconds.

(Formula-approach) My second attempt consisted of an INDIRECT formula
referencing each cell on each sheet that could have a value. The
problem here is that every time a sheet is modified, the auto-
calculation slows down the sheet dramatically!

I hope I have provided enough information and look forward to any
suggestions.

Thank you

 

[Don Guillett Excel MVP]

All,

I have a VBA-formula hybrid workbook that is used to track employees'
vacation time.  It is calendar-style, with the days of the month 1-3x
running from left to right, months running vertically. For a day off,
you enter in the code for the type of vacation and the number of hours
in the cell below. Each employee has their own sheet and each
department has their own workbook.

I was recently asked to add a 'team calendar' sheet that would
consolidate all employees' time off onto one page so the manager could
see if there were vacation conflicts.

(VBA-approach) I first tried to accomplish this by having a For/Next
loop circle through all appropriate cells per employee sheet and
export to the Team Calendar tab.  This, unfortunately, took a lot of
time to do and was not very efficient. For reference, 5 employees
could take up to 15-20 seconds.

(Formula-approach) My second attempt consisted of an INDIRECT formula
referencing each cell on each sheet that could have a value.  The
problem here is that every time a sheet is modified, the auto-
calculation slows down the sheet dramatically!

I hope I have provided enough information and look forward to any
suggestions.

Thank you

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Clif McIrvin]

All,

I was recently asked to add a 'team calendar' sheet that would
consolidate all employees' time off onto one page so the manager could
see if there were vacation conflicts.

(VBA-approach) I first tried to accomplish this by having a For/Next
loop circle through all appropriate cells per employee sheet and
export to the Team Calendar tab. This, unfortunately, took a lot of
time to do and was not very efficient. For reference, 5 employees
could take up to 15-20 seconds.

My guess is the poor speed has a lot to do with not shutting off the
user interface while running your code.

Don't use .Select or .Activate at all in your code, until you are
exiting and want to set up what the user is going to see. There are
different ways to achieve this result; what I use depends some on how I
feel when I'm coding that day (okay, that's a bit of a joke) and on
exactly what I need to do. With / End With comes in handy; I also use
"Set myRange1 = (some range)" and "Set myRange2 = (some other range)"
and then operate on those variables.

Also, check out the performance tips at
http://blogs.msdn.com/b/excel/archive/2009/03/12/excel-vba-performance-coding-best-practices.aspx

HTH!