Is there a way to write code to select sheets 1 to x number of sheets? I
have code that is counting the number of sheets in a workbook and subtracting
3 of those sheets already which is working.

What I want to do is select the sheets and copy to a new workbook.

Thanks

Steve,

Try something like the following:

Dim Sh1 As String
Dim Sh2 As String

Sh1 = "Sheet1"
Sh2 = "Sheet3"
Sheets(Array(Sh1, Sh2)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steve" wrote in message
...
Is there a way to write code to select sheets 1 to x number of
sheets? I
have code that is counting the number of sheets in a workbook
and subtracting
3 of those sheets already which is working.

What I want to do is select the sheets and copy to a new
workbook.

Thanks

Thanks for the quick reply. This will work if I want to select 2 sheets but
it does not allow me to select a range of worksheets, i.e. - Sheet1, Sheet2,
....., Sheet 23.


"Chip Pearson" wrote:

Steve,

Try something like the following:

Dim Sh1 As String
Dim Sh2 As String

Sh1 = "Sheet1"
Sh2 = "Sheet3"
Sheets(Array(Sh1, Sh2)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steve" wrote in message
...
Is there a way to write code to select sheets 1 to x number of
sheets? I
have code that is counting the number of sheets in a workbook
and subtracting
3 of those sheets already which is working.

What I want to do is select the sheets and copy to a new
workbook.

Thanks

Hi Steve,

Steve wrote:
Dim Sh1 As String
Dim Sh2 As String

Sh1 = "Sheet1"
Sh2 = "Sheet3"
Sheets(Array(Sh1, Sh2)).Select

Thanks for the quick reply. This will work if I want to select 2
sheets but it does not allow me to select a range of worksheets, i.e.
- Sheet1, Sheet2, ...., Sheet 23.

Something like this may do what you want:

Dim asSheets(22) As String
Dim lSht As Long

For lSht = 1 To 23
asSheets(lSht - 1) = "Sheet" & CStr(lSht)
Next lSht

Sheets(asSheets).Select

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi,

I tried Jake's suggestion and it works great. Is there a way to do it
without declaring the number of elements in the array or setting it as a
variable? I want to use it to select and move up 85 worksheets to another
wookbook and save it. I try setting it at the max, but I keep getting an
error -- Run-time error '9': "Subscript out of range"

--
Regards,
Tim


"Jake Marx" wrote:

Hi Steve,

Steve wrote:
Dim Sh1 As String
Dim Sh2 As String

Sh1 = "Sheet1"
Sh2 = "Sheet3"
Sheets(Array(Sh1, Sh2)).Select

Thanks for the quick reply. This will work if I want to select 2
sheets but it does not allow me to select a range of worksheets, i.e.
- Sheet1, Sheet2, ...., Sheet 23.

Something like this may do what you want:

Dim asSheets(22) As String
Dim lSht As Long

For lSht = 1 To 23
asSheets(lSht - 1) = "Sheet" & CStr(lSht)
Next lSht

Sheets(asSheets).Select

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi,

You could make use of the Replace flag.

Sub SelectSheets()

Dim intIndex As Integer
Dim blnReplace As Boolean

blnReplace = True
For intIndex = 2 To 7
Worksheets("Sheet" & intIndex).Select blnReplace
blnReplace = False
Next

End Sub

Cheers
Andy

Steve wrote:
Thanks for the quick reply. This will work if I want to select 2 sheets but
it does not allow me to select a range of worksheets, i.e. - Sheet1, Sheet2,
...., Sheet 23.


"Chip Pearson" wrote:


Steve,

Try something like the following:

Dim Sh1 As String
Dim Sh2 As String

Sh1 = "Sheet1"
Sh2 = "Sheet3"
Sheets(Array(Sh1, Sh2)).Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Steve" wrote in message
...

Is there a way to write code to select sheets 1 to x number of
sheets? I
have code that is counting the number of sheets in a workbook
and subtracting
3 of those sheets already which is working.

What I want to do is select the sheets and copy to a new
workbook.

Thanks




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info