Sheets arrays

[Tomas M.]

Hi,
I create some sheets and move them from source workbook to new one:
Sheets(Array(Range("Sheet1").Value, Range("Sheet2").Value)).Move

My problem is, that I need to get different arrays, as user select them in
some form. So, I found the code:
x = Array("Sheet1", "Sheet5", "Sheet7")
Sheets(x).FillAcrossSheets _
Worksheets("Sheet1").Range("A1:C5")
and I try it to use with '.move' method, but it fails with error '9',
Subscript out of range (but it fails too with the 'FillAcrossSheets' method,
as example above).

Is any solution to give arglist to function Array as a variable, or use
other function as argument list for sheets('sheets Array').move ? I tried it
with split of string, which contain all needed sheet names, and tried give
arglist as string ("name1","name2","name3"), but there is some errors too.

Thanks for help
Tomas M.

(I am sorry for my english ...)

 

[Tom Ogilvy]

dim x as Variant
x = Array("Sheet1", "Sheet5", "Sheet7")
worksheets(x).Move

 

[Tom Ogilvy]

as an example
Sub SelectSheets()

Dim x As Variant, i As Long
ReDim x(0 To 0)
For i = 1 To Worksheets.Count
If i Mod 2 = 0 Then
If Worksheets(i).Visible Then
x(UBound(x)) = Worksheets(i).Name
ReDim Preserve x(0 To UBound(x) + 1)
End If
End If
Next i
ReDim Preserve x(0 To UBound(x) - 1)
Worksheets(x).Select

End Sub

This selects all even numbered visible sheets in the tab order. (any hidden
sheets would still count for purposes of the mod function)

You don't tell me how you identify which sheets to work with. If the user
select the sheets using the mouse (and ctrl or shift keys), then you can
just do

Activewindow.SelectedSheet.Move

--
Regards,
Tom Ogilvy

Thanks, this works. But I need give arglist to array function as some
variable list. Sometimes there will be only Sheet1, sometimes all, sometimes
some more. As I wrote, I tried create some string and split it into arrays,
but then I get two dimensional array, and 'worksheets(x).Move' failed.

Exist some solution to give arglist as variable list (or use another
function, method, ... ; I have not any other idea, and I was not succesfull
on internet nor in this newsgroup) ? Or if there is some possibility to
create new function in current VBProject module from running macro, then run
it, and after that delete it (but i think, that this is not good security
idea ;-] ).

dim x as Variant
x = Array("Sheet1", "Sheet5", "Sheet7")
worksheets(x).Move