ManuHuyghe
asked on
Excel VBA: Select specific multiple sheets
Dear,
I try to select multiple sheets, but i always receive Run Time error 9:Subscript out of range.
When i want to select 2 or more fixed sheet i use:
But in my current case the sheets may vary.
So i created a cell with all the names of the sheets that should be selected. In my case Cell G28 on sheet 9.
I tried several posibilities but receive always the same run time error.
Below the code i tested last:
How can i resolve this problem?
I try to select multiple sheets, but i always receive Run Time error 9:Subscript out of range.
When i want to select 2 or more fixed sheet i use:
ThisWorkbook.Worksheets(Array("<Sheet name 1>", "<Sheet name 2>")).SelectBut in my current case the sheets may vary.
So i created a cell with all the names of the sheets that should be selected. In my case Cell G28 on sheet 9.
I tried several posibilities but receive always the same run time error.
Below the code i tested last:
Dim aSheetnames As Variant
aSheetnames = Sheet9.Range("G28").Value
ThisWorkbook.Worksheets(Array(Sheetnames1)).SelectHow can i resolve this problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This works for me without any problem..
However if you add thisworkbook before then that it will give you an error so you need to select without thisworkbook and the code will work...
Saurabh...
Dim str As String
str = Range("G28").Value
Sheets(Array(str)).SelectHowever if you add thisworkbook before then that it will give you an error so you need to select without thisworkbook and the code will work...
Saurabh...
ASKER
@Rgonzo1971: Your solution works perfect.
@Saurabh: i tried also your sugestion but i still receives the run time error 9
Thanks all for the fast response
@Saurabh: i tried also your sugestion but i still receives the run time error 9
Thanks all for the fast response
ASKER
Dear all,
I use the solution proposed by Rgonzo1971 for a while now and it worked fine.
Until last week.
My project is still in progress, so i'm still adding more and more sheets (up to 19 sheets for the moment and more to come).
After adding and configurating the last 4 sheets, i tried to select 1 of them to print and i receive "Run Time Error '1004': Select method of sheets Class failed".
The strange thing is that when i select 2 of them there is no problem.
The way a work now is:
-I write all the names of the sheets that i want to print in different cells on a sheet named (Memory) and use one cell on that sheet to gether all the names as one.
Example of the result: Report NL,Report FR,Report ENG
-Afterwards i use this code to select them:
What can cause this Run time error?
Thanks in advanced
I use the solution proposed by Rgonzo1971 for a while now and it worked fine.
Until last week.
My project is still in progress, so i'm still adding more and more sheets (up to 19 sheets for the moment and more to come).
After adding and configurating the last 4 sheets, i tried to select 1 of them to print and i receive "Run Time Error '1004': Select method of sheets Class failed".
The strange thing is that when i select 2 of them there is no problem.
The way a work now is:
-I write all the names of the sheets that i want to print in different cells on a sheet named (Memory) and use one cell on that sheet to gether all the names as one.
Example of the result: Report NL,Report FR,Report ENG
-Afterwards i use this code to select them:
Dim Sheetnames As Variant
Sheetnames = Split(Sheet9.Range("P28").Value, ",")
ThisWorkbook.Worksheets(Sheetnames).SelectWhat can cause this Run time error?
Thanks in advanced
How are you defining the list/collection of worksheets to select?