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:


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


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]

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

[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

Sheets(arraySheets).Tab.ColorIndex = 5 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