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 Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi & welcome to MrExcel
Try changing the .Text to .Value
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If C13 contains "EX_A1", "EX_B1" then you will need to split that into two different cells.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Glad you sorted it out & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top