Selecting a group of sheet tabs
 

Hello all,

I am trying to select a group of sheets (sheet tabs) (all sheets are in
?consecutive order? - all in a row, one next to the other) by using sheet
indexes instead of sheet names, then find the next empty cell in a specific
column (on all sheets), then enter a user entered string into that cell on
all sheets. The one possible issue is that every time I use this macro, the
number of sheets I want to select may be different (new sheets may be added,
out-dated sheets may be archived to a different file), so I am trying to
dynamically determine the number of sheets to select and then select them
using "Sheets(array(elements)).select" or "Sheets("Name of Array").select".
Every time I execute one of these lines of code, I get a "Run-time error
'9': Subscript out of range".

To give you some background info on the workbook, it is basically several
driving logs for work. The first sheet is the template that each log is
based off of. The next sheets are the driving logs, one for each week.
After the driving logs, there is a sheet titled "MileageLogTotals" and a
couple miscellaneous sheets after that. The template and the driving logs
are the sheets that I'm trying to select. On all of these sheets, in one
column I have a list of origins/destinations. I am trying to add a new
entry to this list and keep this list identical on all of these sheets.

Following is the code that I have come up with. You may notice that there
are lines of code commented out. Some are just tests to help me see if I am
getting the results I want and some are different ways of trying to do the
same thing. Can anyone help me get this working? What is my problem? Do I
have the wrong syntax for the "Sheets.-------.Select" statements? Is there
a better way of accomplishing this task?

Thank you for any help anyone can provide,

Conan Kelly


Sub SortAndFill()
'
' SortAndFill Macro
' Macro recorded 11/7/2004 by Conan Kelly
'
'
Dim pstrCurrentSheetName As String
Dim pstrSheetName As String
Dim pintIndex As Integer
Dim pintI As Variant
Dim pstrNewEntry As String
Dim pstrTest As String
Dim pintResponse As Integer
pstrTest = ""
'Dim pstrCurrentCell As String

pstrCurrentSheetName = ActiveSheet.Name 'Stores the current
sheetname so it can be set active when macro finishes up
pstrSheetName = "MileageLogTotals"
Worksheets(pstrSheetName).Activate
ActiveSheet.Previous.Select
pintIndex = ActiveSheet.Index

ReDim pintNumberOfSheets(pintIndex) As Integer
For pintI = pintIndex - 1 To 0 Step -1
'For Each pintI In pintNumberOfSheets
pintNumberOfSheets(pintI) = pintI + 1
'pstrTest = pstrTest & "PintNumberOfSheets(" & pintI & ") = " &
pintNumberOfSheets(pintI) & vbCrLf
'pintI = pintI + 1
'ActiveSheet.Previous.Select
Next 'pintI

'MsgBox pstrTest, , "Values"

Sheets(pintNumberOfSheets).Select
'Sheets(Array(pintNumberOfSheets)).Select

pstrNewEntry = InputBox("Please enter a new Origin/Destination.", "New
Entry")
Columns("P:P").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Value = pstrNewEntry

Worksheets(pintIndex).Activate

For pintI = pintIndex To 1 Step -1
Columns("P:P").Select
Selection.Sort Key1:=Range("P1"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
Cells.Find(What:="Home - Phoenix", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
pstrCurrentCell = ActiveCell.Address(True, True)
Range("l5").Select
ActiveCell.Formula = "=IF(OR(C5=" & pstrCurrentCell & ",D5=" &
pstrCurrentCell & "),22,0)"
Range("l5:l27").FillDown
Range("l27").Select
With ActiveCell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
End With
Range("A1").Select
If pintI 1 Then
ActiveSheet.Previous.Select
End If
Next
Worksheets(pstrCurrentSheetName).Activate
End Sub

Selecting a group of sheet tabs
 

Sheets(pintNumberOfSheets).Select
'Sheets(Array(pintNumberOfSheets)).Select

should be

Sheets(pintNumberOfSheets).Select

Doesn't mean you won't get an error, but if the array is defined correctly,
it should work

just to demonstrate, this works:

Sub AA()
Dim list() As Integer
ReDim list(0 To 2)
list(0) = 1
list(1) = 2
list(2) = 3
Worksheets(list).Select
End Sub

--
Regards,
Tom Ogilvy

"Conan Kelly" <CTBarbarin at msn dot com wrote in message
...
Hello all,

I am trying to select a group of sheets (sheet tabs) (all sheets are in
?consecutive order? - all in a row, one next to the other) by using sheet
indexes instead of sheet names, then find the next empty cell in a
specific
column (on all sheets), then enter a user entered string into that cell on
all sheets. The one possible issue is that every time I use this macro,
the
number of sheets I want to select may be different (new sheets may be
added,
out-dated sheets may be archived to a different file), so I am trying to
dynamically determine the number of sheets to select and then select them
using "Sheets(array(elements)).select" or "Sheets("Name of
Array").select".
Every time I execute one of these lines of code, I get a "Run-time error
'9': Subscript out of range".

To give you some background info on the workbook, it is basically several
driving logs for work. The first sheet is the template that each log is
based off of. The next sheets are the driving logs, one for each week.
After the driving logs, there is a sheet titled "MileageLogTotals" and a
couple miscellaneous sheets after that. The template and the driving logs
are the sheets that I'm trying to select. On all of these sheets, in one
column I have a list of origins/destinations. I am trying to add a new
entry to this list and keep this list identical on all of these sheets.

Following is the code that I have come up with. You may notice that there
are lines of code commented out. Some are just tests to help me see if I
am
getting the results I want and some are different ways of trying to do the
same thing. Can anyone help me get this working? What is my problem? Do
I
have the wrong syntax for the "Sheets.-------.Select" statements? Is
there
a better way of accomplishing this task?

Thank you for any help anyone can provide,

Conan Kelly


Sub SortAndFill()
'
' SortAndFill Macro
' Macro recorded 11/7/2004 by Conan Kelly
'
'
Dim pstrCurrentSheetName As String
Dim pstrSheetName As String
Dim pintIndex As Integer
Dim pintI As Variant
Dim pstrNewEntry As String
Dim pstrTest As String
Dim pintResponse As Integer
pstrTest = ""
'Dim pstrCurrentCell As String

pstrCurrentSheetName = ActiveSheet.Name 'Stores the current
sheetname so it can be set active when macro finishes up
pstrSheetName = "MileageLogTotals"
Worksheets(pstrSheetName).Activate
ActiveSheet.Previous.Select
pintIndex = ActiveSheet.Index

ReDim pintNumberOfSheets(pintIndex) As Integer
For pintI = pintIndex - 1 To 0 Step -1
'For Each pintI In pintNumberOfSheets
pintNumberOfSheets(pintI) = pintI + 1
'pstrTest = pstrTest & "PintNumberOfSheets(" & pintI & ") = " &
pintNumberOfSheets(pintI) & vbCrLf
'pintI = pintI + 1
'ActiveSheet.Previous.Select
Next 'pintI

'MsgBox pstrTest, , "Values"

Sheets(pintNumberOfSheets).Select
'Sheets(Array(pintNumberOfSheets)).Select

pstrNewEntry = InputBox("Please enter a new Origin/Destination.", "New
Entry")
Columns("P:P").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Value = pstrNewEntry

Worksheets(pintIndex).Activate

For pintI = pintIndex To 1 Step -1
Columns("P:P").Select
Selection.Sort Key1:=Range("P1"), Order1:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
Cells.Find(What:="Home - Phoenix", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
pstrCurrentCell = ActiveCell.Address(True, True)
Range("l5").Select
ActiveCell.Formula = "=IF(OR(C5=" & pstrCurrentCell & ",D5=" &
pstrCurrentCell & "),22,0)"
Range("l5:l27").FillDown
Range("l27").Select
With ActiveCell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
End With
Range("A1").Select
If pintI 1 Then
ActiveSheet.Previous.Select
End If
Next
Worksheets(pstrCurrentSheetName).Activate
End Sub