Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Name a group of sheets | Excel Discussion (Misc queries) | |||
sort between a group of sheets | Excel Worksheet Functions | |||
Group Sheets | Excel Discussion (Misc queries) | |||
Need group all sheets in a workbook | Excel Programming | |||
Sheets Protection for group!!!!? | Excel Programming |