Processing all worksheets simultaneously

G

Guest

I want to perform similar operation in 5-6 sheets of a
workbook. I am currently following the proces as :

For i = 1 To ActiveWorkbook.Worksheets.Count
processEachWorksheet
If exitFlag = True Then Exit Sub
Next i

ie. I am doing a similar process 5-6 times. Can it be
possible that I i do the processing for each sheet at the
same time? Is something like this possible:
For each sheet...
'do this

If possible will it make any difference in the total time
taken?

Thanks
 
J

JMay

Are you familiar with Worksheet grouping and its benefits?
Hold down the shift-key and clink First and Last sheet for contiguous or
Hold down the Control-key and click individual sheets to be "grouped"...
HTH
 
V

Vasant Nanavati

Warning: if you forget to ungroup the sheets, you may damage your data
beyond repair! Grouping should be used very, very carefully.
 
G

Guest

I understand what your are sayinb by grouping and
ungrouping. But How can i implement this by vba???

Any help would be appreciated
 
V

Vasant Nanavati

It would be useful for you to describe what processing you are doing to the
worksheets.
 
S

SmilingPolitely

Being VERY CAREFUL, if and only if the layout of the data on each
worksheet is the same then something along the lines of :

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

might be helpful.
 
F

Frank Kabel

Hi
I think you meant
...
dim wks as worksheet
for each wks in ActiveWorkbook.worksheets
msgbox wks.name
next


But this is NOT simultnaeously but also processes one sheet after the
other.
 
T

Tom Ogilvy

Grouping really isn't supported in VBA. Some things can be done by using
selection, but for the most part, it is advisable to loop through the sheets
and repeat the action.
 
B

Bill Renaud

Try something like the following: Just make sure to ungroup the worksheets
before exiting the subroutine, regardless of any errors that occur during
processing. Test thoroughly!!!

Sub ProcessWorksheets()
On Error Goto UngroupSheets

'Group the worksheets.
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5",
"Sheet6")).Select

ProcessGroupedWorksheets
If exitFlag = True Then GoTo UngroupSheets

Sheets("Sheet1").Select
'Other miscellaneous ungrouped processing here.

UngroupSheets:
Sheets("Sheet1").Select
End Sub

This will reduce the overall execution time somewhat, since you have
eliminated repeating a lot of interpreted VBA code that probably takes 5% to
10% of the total time. A lot depends on how efficiently your code has been
written (minimization of loops, dot operations, common expressions inside
loops, use of pre-calculated constants in place of inline variables, use of
non-Variant variables, good algorithms, etc.)
 
Top