PDA

View Full Version : Solved: move sheets to new book (array)



vzachin
06-16-2008, 06:27 PM
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
06-16-2008, 11:40 PM
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
06-17-2008, 03:40 AM
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
06-17-2008, 03:48 AM
hi bob,

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

zach