selecting multiple sheets

[Young-Hwan Choi]

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.

 

[John Green]

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

 

[Young-Hwan Choi]

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.

You could use something like the following to move the first 50 sheets

from the active workbook to a new workbook: