Trying to select sheets based on cell value with VBA

[MasterVitez]

Hello there!
I'm working on an Excel table, which has multiple worksheets, and I always have to print only some of them. I wrote a simple cell reference to determine which sheets I have to print (atm. its value is: "EX_A1", "EX_B1").
I wrote a VBA macro which should handle the sheet selection and the printing. Printing is not a problem, the sheet selection which doesnt working for me. The name of the sheets I just wrote above is in the CTRL!C13 cell, and the (non-working) VBA code to select these worksheets is:

ThisWorkbook.Sheets(Array(Worksheets("CTRL").Range("C13").Text)).Select

This gives me a "Run-time error '9': Subscript out of range" error. I tried numerous modifications for this VBA code to make it working, but after hours of trying it still doesnt working.
If I simply put the following code into working, the code works fine, but the sheet selection will always changing, and the sheet names will be found in the CTRL!C13 cell:

ThisWorkbook.Sheets(Array("EX_A1", "EX_B1").Select

Please help me to solve this problem.
Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel
Try changing the .Text to .Value

 

[MasterVitez]

Thanks.
I already tried that before I switched to .Text, it gives me the same error.
Also tried to write the CTRL!D13 value as a string to another cell (eg. A1) with VBA, and use that cell (which is not a formula anymore, but a string) for the sheet selection, but nothing changed, got the same error.

 

[Fluff]

Is the CTRL sheet in Thisworkbook?

 

[MasterVitez]

Yes, and I also tried the whole thing with ActiveWorkbook, getting the same result. I have other commands which are using the CTRL sheet (like copy some text from CTRL to another sheet), they are all working.

 

[Fluff]

If C13 contains "EX_A1", "EX_B1" then you will need to split that into two different cells.

 

[MasterVitez]

The value in C13 is formed by using 7 other cells, each of them has 1 sheet name only. C13 is only merged from these cells.
If I use the original 7 cells with the 7 (or less) sheet names, can I form an array or something for my VBA code?
I tried to experience with this method, but it wasnt working for me. Could you show me a sample how should a code like that look like? Would it be like this? (I'm not at a computer with Excel for a few hours to test it.)

ThisWorkbook.Sheets(Array(Worksheets("CTRL").Range("A1:A7").Value)).Select

 

[Fluff]

You can do it like

   Dim ary As Variant
   ary = Application.Transpose(Range("A1:A7").Value)
   Worksheets(ary).Select

BUT each cell must contain a valid sheet name, otherwise it will crash.

 

[MasterVitez]

Works like a charm. Managed to do the dynamic range selection as well, so its working just as I wanted it to.
Thank you very much for your help.

 

[Fluff]

Glad you sorted it out & thanks for the feedback