Select Sheets via Array Macro

[Frank]

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.

 

[William]

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

NB All sheets must be visible.


--
XL2002
Regards

William

[email protected]

| 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.

 

[Tom Ogilvy]

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

 

[William]

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

[email protected]

|
| Rather than using an array, why not use....
| ActiveWorkbook.Sheets.Select
|
| NB All sheets must be visible.
|
|
| --
| XL2002
| Regards
|
| William
|
| [email protected]
|
| | | 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.
|
|