select/group sheets dependent on criteria

  • Thread starter Thread starter Trevor Shuttleworth
  • Start date Start date
T

Trevor Shuttleworth

Hi all, I wonder if you could offer some advice, please.

I am generating a number of worksheets within a workbook. I then want to
group some of them and copy them to another workbook. The names of the
worksheets I am interested in all end in "DWM".

If I manually select the sheets and record the actions, I get:

Sheets(Array("Sunderland DWM", "Manchester DWM", "Coventry DWM")).Select


So, I thought, I just need to loop through the sheets and build the string
....

Dim ArrayList as String
For Each Sheet In Sheets
If Right(Sheet.Name, 3) = "DWM" And Sheet.Name <> "Blank DWM" Then
If ArrayList = "" Then
ArrayList = """" & Sheet.Name & """"
Else
ArrayList = ArrayList & ", " & """" & Sheet.Name & """"
End If
End If
Next

But, when I try to select the sheets based on the string I have built up ...

Sheets(Array(ArrayList)).Select

.... I get Run-time error '9': Subscript out of range

ArrayList contains: "Sunderland DWM", "Manchester DWM", "Coventry DWM"

Any suggestions as to how to get this to work ... or offer a better way ?

Thanks and Regards

Trevor
 
Trevor,
You are using a String and Excel requires an Array...
Sub ABC()
Dim ArrayList() As String
Dim N As Long
Dim sht As Object

ReDim ArrayList(1 To Sheets.Count)
For Each sht In Sheets
If Right$(sht.Name, 3) = "DWM" And sht.Name <> "Blank DWM" Then
N = N + 1
ArrayList(N) = sht.Name
End If
Next
ReDim Preserve ArrayList(1 To N)
Sheets(ArrayList).Select
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(color sort, compare, unique, thesaurus and other add-ins)



"Trevor Shuttleworth" <[email protected]>
wrote in message
Hi all, I wonder if you could offer some advice, please.
I am generating a number of worksheets within a workbook. I then want to
group some of them and copy them to another workbook. The names of the
worksheets I am interested in all end in "DWM".
If I manually select the sheets and record the actions, I get:
Sheets(Array("Sunderland DWM", "Manchester DWM", "Coventry DWM")).Select
So, I thought, I just need to loop through the sheets and build the string
....
Dim ArrayList as String
For Each Sheet In Sheets
If Right(Sheet.Name, 3) = "DWM" And Sheet.Name <> "Blank DWM" Then
If ArrayList = "" Then
ArrayList = """" & Sheet.Name & """"
Else
ArrayList = ArrayList & ", " & """" & Sheet.Name & """"
End If
End If
Next

But, when I try to select the sheets based on the string I have built up ...
Sheets(Array(ArrayList)).Select
.... I get Run-time error '9': Subscript out of range
ArrayList contains: "Sunderland DWM", "Manchester DWM", "Coventry DWM"
Any suggestions as to how to get this to work ... or offer a better way ?
Thanks and Regards
Trevor
 
Jim

thank you very much for the explanation and the solution. Works perfectly.

Regards

Trevor
 
Back
Top