Sheets(array(aryMySheets)).Copy problem!!

[matthew.larkin]

Hi,

I've got some code which runs through some cells to compile a list of
sheets within a workbook that I want to copy out into a separate
workbook.

I've been compiling a string of the relevant worksheet names that I
want, but when I pass this string through
sheets(array(aryMySheets)).Copy I get an error.

e.g

Dim aryMySheets as string
[code to loop through sheets which gives me a strPrintSheet comes here]

If arySheets = "" Then
arySheets = chr(34) & strPrintSheet & chr(34)
Else
arySheets = arySheets & ", " & chr(34) & strPrintSheet & chr(34)
End If
'Therefore this gives me the following string as arySheets:-
""Cost_centre_MA51","Cost_centre_MA54","Cost_centre_MA58","Cost_centre_MA60","Cost_centre_MA61","Cost_centre_MA62","Cost_centre_MA63","Cost_centre_MA64""
[back into for..next loop]

'This is where the error occurs - subscript out of range (and yes, the
sheet names are right!)
Sheets(array(arySheets)).Copy

I could do each sheet one by one into a specifically named workbook,
but I would have to rework some other code which I use generically for
a similar application that only requires a single sheet name to work,
so I'd like to avoid this if possible.

An example I have seen from Tom O gives something like varr =
Evaluate("{""" & arySheets & """}") but I get an error 2015 from that.


Any help gratefully received!!

Matt

 

[Jim Cone]

Matt,
This seems to work...
'-------------
Sub MoveThemOut()
Dim strArray() As String
Dim lngCount As Long
Dim lngN As Long
lngCount = Sheets.Count
ReDim strArray(1 To lngCount)

lngCount = 0
For lngN = 1 To Sheets.Count
If InStr(1, Sheets(lngN).Name, "Cost_centre") Then
lngCount = lngCount + 1
strArray(lngCount) = Sheets(lngN).Name
End If
Next

ReDim Preserve strArray(1 To lngCount)
Sheets(strArray()).Copy
End Sub
'----------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Hi,
I've got some code which runs through some cells to compile a list of
sheets within a workbook that I want to copy out into a separate
workbook.
I've been compiling a string of the relevant worksheet names that I
want, but when I pass this string through
sheets(array(aryMySheets)).Copy I get an error.
e.g

Dim aryMySheets as string
[code to loop through sheets which gives me a strPrintSheet comes here]

If arySheets = "" Then
arySheets = chr(34) & strPrintSheet & chr(34)
Else
arySheets = arySheets & ", " & chr(34) & strPrintSheet & chr(34)
End If
'Therefore this gives me the following string as arySheets:-
""Cost_centre_MA51","Cost_centre_MA54","Cost_centre_MA58","Cost_centre_MA60","Cost_centre_MA61","Cost_centre_MA62","Cost_centre_MA63
","Cost_centre_MA64""
[back into for..next loop]

'This is where the error occurs - subscript out of range (and yes, the
sheet names are right!)
Sheets(array(arySheets)).Copy

I could do each sheet one by one into a specifically named workbook,
but I would have to rework some other code which I use generically for
a similar application that only requires a single sheet name to work,
so I'd like to avoid this if possible.
An example I have seen from Tom O gives something like varr =
Evaluate("{""" & arySheets & """}") but I get an error 2015 from that.
Any help gratefully received!!
Matt