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.. |
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.. |
All times are GMT +1. The time now is 10:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com