Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets(array(aryMySheets)).Copy problem!!
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_centr e_MA58","Cost_centre_MA60","Cost_centre_MA61","Cos t_centre_MA62","Cost_centre_MA63","Cost_centre_MA6 4"" [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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets(array(aryMySheets)).Copy problem!!
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 wrote in message 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_centr e_MA58","Cost_centre_MA60","Cost_centre_MA61","Cos t_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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy an array of Sheets | Excel Discussion (Misc queries) | |||
sheets(array).select problem | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Sheets.copy problem | Excel Programming | |||
Array Sheets Copy to new Workbooks | Excel Programming |