Grouping Worksheets

You can manually group the sheets in a workbook by clicking a sheet tab, then holding down Shift or Ctrl and clicking on another sheet tab. Shift groups all the sheets between the two tabs. Ctrl adds just the new sheet to the group. You can also group sheets in VBA by using the Select method of the Worksheets collection in conjunction with the Array function. The following code groups the first, third, and fifth worksheets and makes the third worksheet active:

Worksheets(Array(1, 3, 5)



In addition to this, you can also create a group using the Select method of the Worksheet object. The first sheet is selected in the normal way. Other worksheets are added to the group by using the Select method while setting its Replace parameter to False:

Sub GroupSheets()

Dim asNames(1 To 3) As Dim i As Integer


asNames(1) = "Jan 2007' asNames(2) = "Mar 2007' asNames(3) = "May 2007'


Worksheets(asNames(i; Next i

i).Select Replace:=False

End Sub

This technique is particularly useful when the names have been specified by user input, via a multi-select list box, for example.

One benefit of grouping sheets manually is that any data inserted into the active sheet and any formatting applied to the active sheet is automatically copied to the other sheets in the group. However, only the active sheet is affected when you apply changes to a grouped sheet using VBA code. If you want to change the other members of the group, you need to set up a For Each...Next loop and carry out the changes on each member.

The following code places the value 10 0 into the A1 cell of worksheets with index numbers 1, 3, and 5 and bolds the numbers:

Sub FormatGroup() Dim shts As Sheets Dim wks As Worksheet

For Each wks In shts wks.Range("A1").Value = 100 wks.Range("A1").Font.Bold = True Next wks

End Sub

Was this article helpful?

0 0
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook

Post a comment