Select Sheets via Array Macro
 

For educational purposes,I'm am trying to create a macro
to use in an add-in that will select all of the sheets in
a workbook via use of an array. This macro will work in
the current workbook but does not seem to work when
applied to other workbooks. Would anyone have advice on
how this macro could be modified so that it could be
placed in the add-in and works when applied to other
workbooks?

Sub TestSelect()
Dim Sht() As String
Dim Cnt As Long
ReDim Sht(1 To ThisWorkbook.Sheets.Count)
For Cnt = LBound(Sht) To UBound(Sht)
ThisWorkbook.Sheets.Select
Next Cnt
End Sub

Thanks for your help.

Select Sheets via Array Macro
 

Rather than using an array, why not use....
ActiveWorkbook.Sheets.Select

NB All sheets must be visible.


--
XL2002
Regards

William



"Frank" wrote in message
...
| For educational purposes,I'm am trying to create a macro
| to use in an add-in that will select all of the sheets in
| a workbook via use of an array. This macro will work in
| the current workbook but does not seem to work when
| applied to other workbooks. Would anyone have advice on
| how this macro could be modified so that it could be
| placed in the add-in and works when applied to other
| workbooks?
|
| Sub TestSelect()
| Dim Sht() As String
| Dim Cnt As Long
| ReDim Sht(1 To ThisWorkbook.Sheets.Count)
| For Cnt = LBound(Sht) To UBound(Sht)
| ThisWorkbook.Sheets.Select
| Next Cnt
| End Sub
|
| Thanks for your help.

Select Sheets via Array Macro
 

thisworkbook refers to the addin (the workbook containing the code)

If you are going to do Sheets.Select, there is no reason to loop.


Sub TestSelect()
ActiveWorkbook.sheets.Select
end Sub

--
Regards,
Tom Ogilvy

"Frank" wrote in message
...
For educational purposes,I'm am trying to create a macro
to use in an add-in that will select all of the sheets in
a workbook via use of an array. This macro will work in
the current workbook but does not seem to work when
applied to other workbooks. Would anyone have advice on
how this macro could be modified so that it could be
placed in the add-in and works when applied to other
workbooks?

Sub TestSelect()
Dim Sht() As String
Dim Cnt As Long
ReDim Sht(1 To ThisWorkbook.Sheets.Count)
For Cnt = LBound(Sht) To UBound(Sht)
ThisWorkbook.Sheets.Select
Next Cnt
End Sub

Thanks for your help.

Select Sheets via Array Macro
 

Frank

I should have added that your code refers to "ThisWorkbook". I think you
mean it to be "ActiveWorkbook." This is at least one reason why your code
fails.

--
XL2002
Regards

William



"William" wrote in message
...
|
| Rather than using an array, why not use....
| ActiveWorkbook.Sheets.Select
|
| NB All sheets must be visible.
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Frank" wrote in message
| ...
| | For educational purposes,I'm am trying to create a macro
| | to use in an add-in that will select all of the sheets in
| | a workbook via use of an array. This macro will work in
| | the current workbook but does not seem to work when
| | applied to other workbooks. Would anyone have advice on
| | how this macro could be modified so that it could be
| | placed in the add-in and works when applied to other
| | workbooks?
| |
| | Sub TestSelect()
| | Dim Sht() As String
| | Dim Cnt As Long
| | ReDim Sht(1 To ThisWorkbook.Sheets.Count)
| | For Cnt = LBound(Sht) To UBound(Sht)
| | ThisWorkbook.Sheets.Select
| | Next Cnt
| | End Sub
| |
| | Thanks for your help.
|
|