Solved: move sheets to new book (array)

[vzachin]

hi,

i need to move all sheets except for two sheet name ("Red" & "Brown") into a new book.
with the macro recorder, i have the following:
[vba]
Sheets(Array("Sheet38", "Sheet37", "Sheet36", "Sheet35", "Sheet34", "Sheet33")). _
Select
Sheets("Sheet33").Activate
Sheets(Array("Sheet38", "Sheet37", "Sheet36", "Sheet35", "Sheet34", "Sheet33")).Move
Application.WindowState = xlMinimized
Windows("save sheet test.xls").Activate
ActiveWorkbook.Save
[/vba]

how do i create an array for all the sheets that i want to move? they will have different names each time.


thanks
zach

[Bob Phillips]

[vba]

Sub Macro1()
Dim arraySheets As Variant
Dim i As Long
Dim j As Long

With ActiveWorkbook

ReDim arraySheets(1 To .Sheets.Count)
j = 0
For i = 1 To .Sheets.Count

If .Sheets(i).Name <> "Red" And .Sheets(i).Name <> "Brown" Then

j = j + 1
arraySheets(j) = Sheets(i).Name
End If
Next i
ReDim Preserve arraySheets(1 To j)
End With

Sheets(arraySheets).Move
ActiveWorkbook.Save
End Sub
[/vba]

[vzachin]

hi bob,

thanks again for helping me out. i can use this code to delete the sheets as well

one final question:
i tried to set the colorindex
[vba]Sheets(arraySheets).Tab.ColorIndex = 5[/vba] but that didn't work.
how do i re-write this?


thanks
zach

[vzachin]

hi bob,

never mind. the question i had didn't make sense. i don't need an array

zach