Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Array Selection
I would like to select all sheets specified in a cell range. The sheets
needed to be selected change on differenet criteria, so I have used a formula to concatenate the sheet names that need to be selected. In the VBA I defined that cell as a string and then used the sheets(array(x)).select. Here is an example: Workbook contains 3 sheets named: "Test 1", "Test 2", and "Test 3" The value in "Test 1" Range A1 = "Test 2", "Test 3" Sub SelectSheetsMacro () Sheets("Test 1").Select Dim x as string x = Range("A1") Sheets(Array(x)).Select End Sub I continue to get an error message....please help.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Array Selection
You can't do it that way
Sub SelectSheets() Dim i As Long Dim v() As Variant ReDim v(0 To 0) i = 1 With Worksheets("Sheet1") Do While Not IsEmpty(.Cells(i, 1)) ReDim Preserve v(0 To i - 1) v(i - 1) = .Cells(i, 1).Value i = i + 1 Loop End With Worksheets(v).Select End Sub worked for me. Assumes you will have at least two sheet names and that the first sheet name is in cell A1 of sheet1 with the remainder following immediately below it in column A. -- Regards, Tom Ogilvy "JJ" wrote: I would like to select all sheets specified in a cell range. The sheets needed to be selected change on differenet criteria, so I have used a formula to concatenate the sheet names that need to be selected. In the VBA I defined that cell as a string and then used the sheets(array(x)).select. Here is an example: Workbook contains 3 sheets named: "Test 1", "Test 2", and "Test 3" The value in "Test 1" Range A1 = "Test 2", "Test 3" Sub SelectSheetsMacro () Sheets("Test 1").Select Dim x as string x = Range("A1") Sheets(Array(x)).Select End Sub I continue to get an error message....please help.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array with multiple selection criteria | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Selection or Recordset to Array | Excel Programming | |||
cells not changing selection after sheets array | Excel Programming | |||
reading an arbitrary selection into a 1D array | Excel Programming |