Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ...) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ...) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avg Arrays | Excel Worksheet Functions | |||
Arrays | Excel Discussion (Misc queries) | |||
Help with arrays please | Excel Programming | |||
arrays | Excel Programming | |||
help with arrays | Excel Programming |