Group sheets using code
 

I have several macros that I need to record where I group sheets, then
perform the actions.
I need a code that will group sheets by sheetname range (i.e. "apples"
to "oranges").
I will record myself perfoming the various actions to get the code to
finish it, however, I need a general/ basic grouping code to start off.



Thanx.

Group sheets using code
 

The select method for worksheets has an option argument Replace (true by
Default) which specifies whether you want to group the sheet you are
selecting with the already selected sheet(s). By setting the value to false
you can group...

Sheets("Sheet1").Select False
Sheets("Sheet2").Select False
Sheets("Sheet3").Select False

--
HTH...

Jim Thomlinson


"J.W. Aldridge" wrote:

I have several macros that I need to record where I group sheets, then
perform the actions.
I need a code that will group sheets by sheetname range (i.e. "apples"
to "oranges").
I will record myself perfoming the various actions to get the code to
finish it, however, I need a general/ basic grouping code to start off.



Thanx.

Group sheets using code
 

Function GroupSheets(StartSheet As String, EndSheet As String)
Dim iStart As Long
Dim iEnd As Long
Dim i As Long, j As Long
Dim arySheets

On Error Resume Next
iStart = ActiveWorkbook.Worksheets(StartSheet).Index
iEnd = ActiveWorkbook.Worksheets(EndSheet).Index
On Error GoTo 0
If iStart = 0 Or iEnd = 0 Or iEnd < iStart Then
MsgBox "Invalid"
Exit Function
End If

ReDim arySheets(iEnd - iStart)
For i = iStart To iEnd
arySheets(i - iStart) = ActiveWorkbook.Worksheets(i).Name
Next i

Sheets(arySheets).Select

End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"J.W. Aldridge" wrote in message
oups.com...
I have several macros that I need to record where I group sheets, then
perform the actions.
I need a code that will group sheets by sheetname range (i.e. "apples"
to "oranges").
I will record myself perfoming the various actions to get the code to
finish it, however, I need a general/ basic grouping code to start off.



Thanx.

Group sheets using code
 

I'm sure it will work, but I'm having some trouble figuring out where
to place a function.
I'm used to sub - macro's. Should there be a heading?
As stated before, I will add-in the procedures I need towards the end.


Thanx.

Group sheets using code
 

Place it in a standard code module. It will work just the same as a sub.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"J.W. Aldridge" wrote in message
oups.com...
I'm sure it will work, but I'm having some trouble figuring out where
to place a function.
I'm used to sub - macro's. Should there be a heading?
As stated before, I will add-in the procedures I need towards the end.


Thanx.

Group sheets using code
 

You can just call Bob's code the same as any other code. If you wish you can
even change the word function to sub. The only not is that the sheet Apple
must come before the sheet Orange and none of the sheets in the middle may be
hidden...

sub Test
Call GroupSheets("Apple", "Orange")
end sub

Public Sub GroupSheets(StartSheet As String, EndSheet As String)
Dim iStart As Long
Dim iEnd As Long
Dim i As Long, j As Long
Dim arySheets

On Error Resume Next
iStart = ActiveWorkbook.Worksheets(StartSheet).Index
iEnd = ActiveWorkbook.Worksheets(EndSheet).Index
On Error GoTo 0
If iStart = 0 Or iEnd = 0 Or iEnd < iStart Then
MsgBox "Invalid"
Exit Function
End If

ReDim arySheets(iEnd - iStart)
For i = iStart To iEnd
arySheets(i - iStart) = ActiveWorkbook.Worksheets(i).Name
Next i

Sheets(arySheets).Select

End Sub


--
HTH...

Jim Thomlinson


"J.W. Aldridge" wrote:

I'm sure it will work, but I'm having some trouble figuring out where
to place a function.
I'm used to sub - macro's. Should there be a heading?
As stated before, I will add-in the procedures I need towards the end.


Thanx.