Code for selecting worksheets

[Dave rey]

Hi

I have a workbook with 70 sheets named Sheet1 to Sheet70,
and I am trying to write some code into a macro to allow
me to select (say) only Sheets 15 to 40.

I am trying to write code in the following format, but
struggling:

Sheets ("Sheet15" to "Sheet40").Select

Or in another case:

Sheets("Sheet15 to "last sheet").Select - where last sheet
could be any sheet depending on how many I add or delete
in the middle.

If I try to record a macro by selecting the sheets it uses
the array command, but lists each sheet in the range by
name. This is no good as I am continually adding and
removing sheets.

Thanks

 

[Jack Schitt]

Something like:

Sub SelectSheets()
Dim lSheetCount as Long
Dim sMyArray() as String
With Application.ThisWorkbook
ReDim sMyArray(1 to .Worksheets.Count - .Worksheets("Sheet15").Index
For lSheetCount = 1 to .Worksheets.Count - .Worksheets("Sheet15").Index
sMyArray(lSheetCount) = .Worksheets(.Worksheets("Sheet15").Index +
lSheetCount - 1).Name
Next lSheetCount
..Worksheets(sMyArray).Select
End With 'Application.ThisWorkbook
End Sub 'SelectSheets()

Untested, but that should I think select all sheets 15 to end. To just do
sheets 15 to 40 substitute '("Sheet40")' for '.Count'
It is probably a sledgehammer approach, but gives you something to go on.

 

[Jack Schitt]

That should of course be
substitute '("Sheet40").Index' for '.Count'
but I am sure you get the drift.
Also there was an unintentional line break after a "+" character

 

[Jack Schitt]

Should be a close bracket ")" at the end of the redim line.
Sorry

 

[Dave Rey]

Thanks for the reply and help.

I've just tried the code and it keeps giving me a syntax
error on the line "ReDim sMyArray etc".

I am very new to VBA code and must admit I've no idea how
to correct it.

Thanks

Dave

 

[Guest]

yep - thats it. Seems to work fine now.

Thanks very much for your help - very much appreciated.

Dave