Here's some of the work that I've done so far. The procedure below (a very quickly knocked-up one) will create the text string which contains the sheet names in the standard "sheet1", "sheet2", "sheet3"... format.
How on earth do I pass this through to the sheets collection so that it selects the required sheets?
Here's the code:
Public Sub TestSheetsCopy()
Dim strSheets As String, w
strSheets = """"
For Each w In ActiveWorkbook.Worksheets
Select Case w.Name
Case "Control Screen", "AllData", "Pivots", "Data"
'Do Nothing
Case Else
strSheets = strSheets & w.Name & """, """
End Select
Next
strSheets= Left(strSheets, Len(strSheets) - 3)
MsgBox strSheets
'Sheets(Array(strSheets)).Select - doesn't work
'Sheets(strSheets).Select - doesn't work
End Sub
Any ideas?
Ad