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!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
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
46,578
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
46,578
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
46,578
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it out & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,434
Messages
5,528,742
Members
409,832
Latest member
Misspears10

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top