Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba for selecting multiple sheets to save as a new book | Excel Worksheet Functions | |||
Selecting Multiple Sheets in VBA | Excel Worksheet Functions | |||
selecting multiple sheets | Excel Worksheet Functions | |||
Selecting sheets - II | Excel Discussion (Misc queries) | |||
Changing the value in multiple sheets without selecting those sheets | Excel Programming |