Trying to select sheets based on cell value with VBA

MasterVitez

New Member
Joined
Feb 18, 2019
Messages
5
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:
Code:
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:
Code:
ThisWorkbook.Sheets(Array("EX_A1", "EX_B1").Select

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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
Try changing the .Text to .Value
 
Last edited:

MasterVitez

New Member
Joined
Feb 18, 2019
Messages
5
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.
 

MasterVitez

New Member
Joined
Feb 18, 2019
Messages
5

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
If C13 contains "EX_A1", "EX_B1" then you will need to split that into two different cells.
 

MasterVitez

New Member
Joined
Feb 18, 2019
Messages
5

ADVERTISEMENT

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.)
Code:
ThisWorkbook.Sheets(Array(Worksheets("CTRL").Range("A1:A7").Value)).Select
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
You can do it like
Code:
   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

New Member
Joined
Feb 18, 2019
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,443
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it out & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,576
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top