selecting multiple sheets
 

How do I select multiple sheets?
What I want to do is to move some sheets to a new workbook.

Moving the sheets is inside For ~ next. Generating several sheets is also in
the For ~ Next. The number of sheets changes. When I record a macro for this
job, the code looks like Sheets(Array(sheet1, sheet2,.....)) According to
the recording, I have to type all the sheets I want to move.

Is there any better method for selecting multiple sheets, let's say from
sheets1 to sheets 50, like Range("A1:A30")?

thanks.

selecting multiple sheets
 

You could use something like the following to move the first 50 sheets from the active workbook to a new workbook:

Sub MoveSheets()
Dim shtArray() As Integer
Dim i As Integer

For i = 1 To 50
ReDim Preserve shtArray(1 To i)
shtArray(i) = i
Next i

Sheets(shtArray).Move
End Sub


--

John Green - Excel MVP
Sydney
Australia


"Young-Hwan Choi" wrote in message ...
How do I select multiple sheets?
What I want to do is to move some sheets to a new workbook.

Moving the sheets is inside For ~ next. Generating several sheets is also in
the For ~ Next. The number of sheets changes. When I record a macro for this
job, the code looks like Sheets(Array(sheet1, sheet2,.....)) According to
the recording, I have to type all the sheets I want to move.

Is there any better method for selecting multiple sheets, let's say from
sheets1 to sheets 50, like Range("A1:A30")?

thanks.

selecting multiple sheets
 

John,

I've just changed "i=1 to 50" to "i = 8 to sheets.count" and "shtArrary(1 To
i)" to "shtArray(8 to i)" in your coding, as I want to move 8th ~ the last
sheet to the new book.
Although I'm not sure whether what I've done is right or not, it does what I
want beautifully.

I appreciate your help.

regards,
choi.

"John Green" wrote in message
...
You could use something like the following to move the first 50 sheets
from the active workbook to a new workbook:

Sub MoveSheets()
Dim shtArray() As Integer
Dim i As Integer

For i = 1 To 50
ReDim Preserve shtArray(1 To i)
shtArray(i) = i
Next i

Sheets(shtArray).Move
End Sub


--

John Green - Excel MVP
Sydney
Australia


"Young-Hwan Choi" wrote in message
...
How do I select multiple sheets?
What I want to do is to move some sheets to a new workbook.

Moving the sheets is inside For ~ next. Generating several sheets is
also in
the For ~ Next. The number of sheets changes. When I record a macro for
this
job, the code looks like Sheets(Array(sheet1, sheet2,.....)) According
to
the recording, I have to type all the sheets I want to move.

Is there any better method for selecting multiple sheets, let's say from
sheets1 to sheets 50, like Range("A1:A30")?

thanks.