Link to home
Start Free TrialLog in
Avatar of ManuHuyghe
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:
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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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

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?
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Is the sheet you want to select hidden?

Regards
Avatar of ManuHuyghe

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 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?
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

Open in new window

@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
Does the code I posted work at all or does it fail immediately?
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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'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.