software

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 quot;STARTquot; and quot;ENDquot;
(these will have user defined names so cannot be specified in the macro) and
then 2 additional pre-defined tabs.

Any ideas anyone?

Giz

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 quot;first_tempquot; and quot;last_tempquot;, position them at the
appropriate places, then make use of these. Once you have finished,
quot;first_tempquot; and quot;last_tempquot; could be deleted.

Hope this helps.

PeteSub 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(quot;SheetStartquot;).Index
lngEnd = Worksheets(quot;SheetFinishquot;).Index
If lngEnd lt; lngStart Then
MsgBox quot;Please reorder sheets. quot;
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
www.realezsites.com/bus/primitivesoftwarequot;Gizmo63quot;
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 quot;STARTquot; and quot;ENDquot;
(these will have user defined names so cannot be specified in the macro) and
then 2 additional pre-defined tabs.
Any ideas anyone?
Giz

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

quot;Pete_UKquot; wrote:

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

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 quot;objShtsquot; stuff to make it work.
A few tweaks to bypass the tab indexes and sorted.

Cheers

Giz

quot;Jim Conequot; wrote:

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