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:
Dim Sheetnames As Variant
Sheetnames = Split(Sheet9.Range("P28").Value, ",")
ThisWorkbook.Worksheets(Sheetnames).Select

Open in new window

What can cause this Run time error?

Thanks in advanced
ManuHuygheAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what are you trying to achieve by selecting the worksheets? in fact, you can refer to the worksheet object without selecting them... like:
For Each ws In Worksheets
        Debug.Print ws.Name
       'Do your stuff here
    Next

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
ThisWorkbook.Worksheets(Sheetnames).Select will select all the sheets in the array of sheets so what method you are using to select only 1 sheet?
Rgonzo1971Commented:
Hi,

Is the sheet you want to select hidden?

Regards
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

ManuHuygheAuthor Commented:
@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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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"
What code do you use to select those sheets?
Can you share the code which is not working and producing an error?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Even better if you upload a sample workbook with the code so that we can test the code for you.
NorieAnalyst Assistant Commented:
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

Open in new window

ManuHuygheAuthor Commented:
@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:
ThisWorkbook.Worksheets(Sheetnames).Select

Open in new window


@Norie
I tried the code, but the result stays the same.
New-Microsoft-Excel-Worksheet.xlsm
NorieAnalyst Assistant Commented:
Does the code I posted work at all or does it fail immediately?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Cannot run the code as some routines and sheets are missing from the workbook.

Also I noticed that there is no Sheetnames variable defined in the code and I didn't find the line of code you are mentioning i.e. ThisWorkbook.Worksheets(Sheetnames).Select

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManuHuygheAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.