ManuHuyghe
asked on
Excel VBA: Select specific multiple sheets bis
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:
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).Select
What can cause this Run time error?Thanks in advanced
ThisWorkbook.Worksheets(Sh eetnames). Select will select all the sheets in the array of sheets so what method you are using to select only 1 sheet?
Hi,
Is the sheet you want to select hidden?
Regards
Is the sheet you want to select hidden?
Regards
ASKER
@Ryan Chong
I need to do a print out (one pdf) of all the selected sheets. And the sheets that i need to select is in function of which documents that our technicians need to deliver to the client. So the combination of which sheets to print can be very different.
@sktneer
I always select minimum 2 sheets, because the first sheet is the my front page layout.
When i talked earlier about 1 of the new sheets, it's actually "my front page + 1 of the new sheets"
@Rgonzo1971
No, all sheets are visible.
Our technicians can only see the userforms, so there is no need to hide sheets.
Thanks for the fast response.
I need to do a print out (one pdf) of all the selected sheets. And the sheets that i need to select is in function of which documents that our technicians need to deliver to the client. So the combination of which sheets to print can be very different.
@sktneer
I always select minimum 2 sheets, because the first sheet is the my front page layout.
When i talked earlier about 1 of the new sheets, it's actually "my front page + 1 of the new sheets"
@Rgonzo1971
No, all sheets are visible.
Our technicians can only see the userforms, so there is no need to hide sheets.
Thanks for the fast response.
I always select minimum 2 sheets, because the first sheet is the my front page layout.What code do you use to select those sheets?
When i talked earlier about 1 of the new sheets, it's actually "my front page + 1 of the new sheets"
Can you share the code which is not working and producing an error?
Even better if you upload a sample workbook with the code so that we can test the code for you.
Does this work?
Dim Sheetnames As Variant
Dim I As Long
Sheetnames = Split(Sheet9.Range("P28").Value, ",")
For I = LBound(SheetNames) To Ubound(SheetNames)
ThisWorkbook.Worksheets(Sheetnames(I)).Select False
Next I
ASKER
@sktneer
Attached, you can find a workbook with the codes i use.
-On sheet1 you can see how i create the cell with all the names of the sheets that i want to print.
-In the progressbar userform you can see the formula that write all the names of the nessecary sheets and how i activate the module to print.
-The module to print is also in the workbook
The line in the module that gives me the runtime error is:
@Norie
I tried the code, but the result stays the same.
New-Microsoft-Excel-Worksheet.xlsm
Attached, you can find a workbook with the codes i use.
-On sheet1 you can see how i create the cell with all the names of the sheets that i want to print.
-In the progressbar userform you can see the formula that write all the names of the nessecary sheets and how i activate the module to print.
-The module to print is also in the workbook
The line in the module that gives me the runtime error is:
ThisWorkbook.Worksheets(Sheetnames).Select
@Norie
I tried the code, but the result stays the same.
New-Microsoft-Excel-Worksheet.xlsm
Does the code I posted work at all or does it fail immediately?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear,
I found the problem.
I'm working with multiple workbooks and i don't know the exact reason why but only when i use this userform the focus returns to another workbook in stead of my main workbook. So i added thisworkbook.activate before runing the code and the formula works.
Thanks for the good proposals.
I found the problem.
I'm working with multiple workbooks and i don't know the exact reason why but only when i use this userform the focus returns to another workbook in stead of my main workbook. So i added thisworkbook.activate before runing the code and the formula works.
Thanks for the good proposals.
I've requested that this question be closed as follows:
Accepted answer: 0 points for ManuHuyghe's comment #a41044952
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Accepted answer: 0 points for ManuHuyghe's comment #a41044952
for the following reason:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Open in new window