Selecting Multiple Sheets in VBA

[Gizmo63]

Hi folks,

Is there a way of selecting a variable number of worksheets in VB so that I
can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix
includes inserting some columns and the references will go screwy.
It is possible to do manually so should be possible in VB. The equivalent of
holding the control key and clicking on the tabs you want.

I would be looking at 'selecting' all the tabs between "START" and "END"
(these will have user defined names so cannot be specified in the macro) and
then 2 additional pre-defined tabs.

Any ideas anyone?

Giz

[Pete_UK]

If you can do it manually, why can't you record a macro while you do
it? Then you will be able to see the syntax and should be able to merge
the recorded macro (or parts of it) into your other macro. You could
insert two sheets "first_temp" and "last_temp", position them at the
appropriate places, then make use of these. Once you have finished,
"first_temp" and "last_temp" could be deleted.

Hope this helps.

Pete

[Jim Cone]

Sub JustTheOnesIWant()
'Selects all sheets between two designated sheets.
'Jim Cone - San Francisco, USA - May 2006
Dim objShts As Excel.Sheets
Dim varArry As Variant
Dim lngStart As Long
Dim lngEnd As Long
Dim N As Long

lngStart = Worksheets("SheetStart").Index
lngEnd = Worksheets("SheetFinish").Index
If lngEnd < lngStart Then
MsgBox "Please reorder sheets. "
Exit Sub
End If
ReDim varArry(lngStart To lngEnd)

For N = lngStart To lngEnd
varArry(N) = Worksheets(N).Name
Next

Set objShts = Sheets(varArry)
objShts.Select
Set objShts = Nothing
End Sub
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Gizmo63"
wrote in message
Hi folks,

Is there a way of selecting a variable number of worksheets in VB so that I
can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix
includes inserting some columns and the references will go screwy.
It is possible to do manually so should be possible in VB. The equivalent of
holding the control key and clicking on the tabs you want.

I would be looking at 'selecting' all the tabs between "START" and "END"
(these will have user defined names so cannot be specified in the macro) and
then 2 additional pre-defined tabs.
Any ideas anyone?
Giz

[Gizmo63]

Hi Pete,

Won't work for this. The macro builds an array statement that picks up the
tab names as you click then and use the defined names in the macro.

There is no variabilty in it.

Giz

"Pete_UK" wrote:

> If you can do it manually, why can't you record a macro while you do
> it? Then you will be able to see the syntax and should be able to merge
> the recorded macro (or parts of it) into your other macro. You could
> insert two sheets "first_temp" and "last_temp", position them at the
> appropriate places, then make use of these. Once you have finished,
> "first_temp" and "last_temp" could be deleted.
>
> Hope this helps.
>
> Pete
>
>

[Gizmo63]

Thanks Jim.

I was pretty close to the solution, heading on the right lines of cycling
and saving the names but didn't know the "objShts" stuff to make it work.
A few tweaks to bypass the tab indexes and sorted.

Cheers

Giz

"Jim Cone" wrote:

> Sub JustTheOnesIWant()
> 'Selects all sheets between two designated sheets.
> 'Jim Cone - San Francisco, USA - May 2006
> Dim objShts As Excel.Sheets
> Dim varArry As Variant
> Dim lngStart As Long
> Dim lngEnd As Long
> Dim N As Long
>
> lngStart = Worksheets("SheetStart").Index
> lngEnd = Worksheets("SheetFinish").Index
> If lngEnd < lngStart Then
> MsgBox "Please reorder sheets. "
> Exit Sub
> End If
> ReDim varArry(lngStart To lngEnd)
>
> For N = lngStart To lngEnd
> varArry(N) = Worksheets(N).Name
> Next
>
> Set objShts = Sheets(varArry)
> objShts.Select
> Set objShts = Nothing
> End Sub
> ------------
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Gizmo63"
> wrote in message
> Hi folks,
>
> Is there a way of selecting a variable number of worksheets in VB so that I
> can apply a simultaneous fix. I can't do the sheets 1 at a time as the fix
> includes inserting some columns and the references will go screwy.
> It is possible to do manually so should be possible in VB. The equivalent of
> holding the control key and clicking on the tabs you want.
>
> I would be looking at 'selecting' all the tabs between "START" and "END"
> (these will have user defined names so cannot be specified in the macro) and
> then 2 additional pre-defined tabs.
> Any ideas anyone?
> Giz
>