Selecting sheets based on value in cell

StianU

New Member
Joined
Jan 25, 2019
Messages
4
Hi,

I have a workbook where I want to print selected sheets, based on worksheets named in a cell i Excel.
In cell A1 in Worksheet1 I have the names of the sheets I want to select, seperated by comma. The value in cell A1 is "Report1,Report2,Report3" and so on, and it changes based on inputs in different cells.

I have tried this formula, but I get Runtime error 9. Anyone what am I doing wrong?

Sub SelectSheets()
Dim wrLocal As Workbook
Dim shInput As Worksheet
Dim Sheetnames As Variant

Set wrLocal = ThisWorkbook
Set shInput = wrLocal.Worksheets("INPUT")

Sheetnames = shInput.Range("A1")
Sheetnames = Application.WorksheetFunction.Transpose(Sheetnames)
Worksheets(Sheetnames).Select

End Sub
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
Try
replacing
Code:
Sheetnames = shInput.Range("A1")

with
Code:
Sheetnames = Split(shInput.Range("A1"), ",")
This will select the 3 sheets
 

Watch MrExcel Video

Forum statistics

Threads
1,098,998
Messages
5,465,897
Members
406,453
Latest member
CeReaLK

This Week's Hot Topics

Top