Grouping multiple worksheets using VBA.

[Alianza]

(I'm using Excel 2010 (32 bit) on Windows 7.)

I have several workbooks which I have set up sets of like worksheets between "separator" tabs so I can sum across worksheets and perform other functions across the worksheets. The arrangement of worksheets (tabs) is something like this: StartTab, WorkTab1, WorkTab2, ..., WorkTabn,EndTab. The thing is that the various workbooks I use have different number of "WorkTabs" in between the "separator' tabs (StartTab and EndTab). I need to write a macro that includes grouping all the tabs in between the "separator" tabs. Unfortunately everything I've seen does this through a statement like:

Sheets(Array("StartTab","WorkTab1","WorkTab2","EndTab")).Select

Since the the number of "WorkTabs" is different in all my workbooks, and the worksheet names vary, I can't use this appraoch. Is there a way to write a VBA statement that will group all the worksheets in between my two "separator" tabs.

Thanks in advance.

 

[JoeMo]

Try:

Sub GroupTabs()
Dim sh As Worksheet
For i = Sheets("StartTab").Index To Sheets("Endtab").Index
    Sheets(i).Select Replace:=False
Next i
End Sub

 

[Alianza]

Thank you, it worked like a charm, and it is elegant in its simplicity.

 

[JoeMo]

Thank you, it worked like a charm, and it is elegant in its simplicity.

You're welcome. BTW: you don't need the Dim statement at the start, just forgot to delete it after I changed my mind about taking a different approach.

 

[Peter_SSs]

Welcome to the MrExcel board!

Sounds like you may not have any other sheets outside the 'separator' tabs. However, in case you do, or might in the future, note that the existing code will include an 'outside' sheet in your grouped sheets if it is already selected when the code is run. Just to be on the safe side, you might consider this slight variation.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> GroupTabs()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Sheets("StartTab").Select Replace:=<SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = Sheets("StartTab").Index + 1 <SPAN style="color:#00007F">To</SPAN> Sheets("Endtab").Index<br>        Sheets(i).Select Replace:=<SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Alianza]

Thanks, I just tried it both ways with other sheets selected before executing the macro. Your fix works well.

 

[GeorgiaHOKIE]

Hi, I'm trying to create a macro that will create worksheets based off of the amount rows in data set, and then group multiple worksheets using VBA, but the exact number of worksheets will vary everytime i use the macro. My problem is, if I do not have the exact number of worksheets (ie say I run the macro on one set of data that has 13 rows which will generate 13 worksheets, but the next time I run the macro with a different amount of rows, say 40) the macro won't run since in my original number of rows in the first macro was 13. Is there anyway to fix this so that I do not need to know the exact number of data points? Here is my original code:

Sub RowToSheet()
Dim xRow As Long
Dim i As Long
With ActiveSheet
xRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To xRow
Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & i
.Rows(i).Copy Sheets("Row " & i).Range("A1")
Next i
Sheets("Row 2").Select Replace:=True
For i = Sheets("Row 2").Index To Sheets("Row 13").Index
Sheets(i).Select Replace:=False
Next i
Sheets("Row 2").Activate
Range("A4").Select
ActiveCell.FormulaR1C1 = _
"=BDS(R[-3]C[2]&"" Corp"",""ALL_HOLDERS_PUBLIC_FILINGS"")"
End With
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

There would be several other ways to achieve your result, but keeping most of your code as is, you could just make this small change.
Change this

For i = Sheets("Row 2").Index To Sheets("Row 13").Index

to this

For i = Sheets("Row 2").Index To Sheets("Row " & xRow).Index

or this

For i = Sheets("Row 2").Index + 1 To Sheets.Count

When posting code, please use code tags to make it easier to read/debug. My signature block below shows how to do that.