Select Sheet Tabs Dynamically

[sapads]

I'm using the code below to select a number of worksheets. Is it possible to list the sheetnames on a sheet called "Report" rather than hard code them in? The names will change as will the number of sheets I need to select.

I've spent hours looking on the internet but cannot find an answer to this question.


Sheets(Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Week 01", "Week 02", "Week 03", "Week 04", "Week 05")).Select

[mdmackillop]

Have a look at this post (http://www.vbaexpress.com/forum/showthread.php?t=34636)

[sapads]

Thanks for the suggestion but it's not what I'm after. I need to be able to list the sheetnames in a range of cells and for this to be used in the array.

This is what I've tried so far.


Sub test1()
Dim MyArray As Variant
MyArray = Array(Sheets("Sheet1").Range("A1:A12"))
'Sheets(Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Week 01", "Week 02", "Week 03", '"Week 04", "Week 05")).Select
Sheets(MyArray).Select
End Sub


Hope someone can assist further.

[Kenneth Hobs]

This method ran from a sheet adds hyperlinks to the sheets. Of course it is easy to just list and navigate by a rightclick on the navigation arrows. Just change ActiveSheet to your Report's sheet name. If you want to use the sheet name on the tag replace with Worksheets(Report).

Sub ListSheetNames()
Dim i As Long
For i = 1 To Sheets.Count
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:="#'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
Next i
End Sub

You could add some code to remove all or part of the Report sheet prior to this. Of course this could be triggered by a change to that sheet as well.

[sapads]

Thanks again but not what I'm after.

I need to enter the sheetnames manually in cells "A1:A12" so that I can decide which sheets are selected.

The workbook I'm using will have over 50 worksheets so I need to be able to decide manually which ones I want selecting.

[mdmackillop]

Sub SelectSheets()
Dim Cel As Range
Dim arrSheets()
Dim i
ReDim arrSheets(Range("SelSheets").Count)
For Each Cel In Range("SelSheets")
arrSheets(i) = Cel
i = i + 1
Next
ReDim Preserve arrSheets(i - 1)
Sheets(arrSheets).Select
End Sub

[Kenneth Hobs]

You should probably use a listbox or something else like that I suspect.

So, if I read what you asked for:

Sub SelectReportSheets()
Dim r As Range, cell As Range, ws As Sheets
Set r = Worksheets("Report").Range("A1", Worksheets("Report").Range("A" & Rows.Count).End(xlUp))
Set ws = Sheets(WorksheetFunction.Transpose(r))
ws.Select
'MsgBox ws.Count
'application.CutCopyMode=false
'Set r = Nothing
'Set ws = Nothing
End Sub

[sapads]

Thankyou, that work's for me.:clap: