Sheets arrays
 

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 ...)

Sheets arrays
 

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


--
Regards
Tom Ogilvy

"Tomas M." <Tomas wrote in message
...
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 ...)

Sheets arrays
 

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




"Tomas M." <Tomas wrote in message
...
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 ;-] ).

"Tom Ogilvy" wrote:

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