please help

[mrmac]

I'm am trying to programmatically select several sheets
in a workbook. The recorded macro looks like this:

sub macro1()
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
end sub

The macro I wrote to mimic that, gives me a Subscript out
of range error. What am I doing wrong?

Sub macro()
Dim chrts as String
chrts = Chr(34)
For x = 1 To Sheets.Count
chrts = chrts & Sheets(x).Name & Chr(34) & Chr(44) _
& Chr(32) & Chr(34)
Next x
chrts = Left(chrts, Len(chrts) - 3)
Sheets(Array(chrts)).Select
End Sub

Any help is really appreciated.

 

[Rob Bovey]

To answer your immediate question, you don't need to mimic the Array
function. Just use a real array loaded with the names of all the sheets you
want to select as follows:

Sub SelectSheets()
Dim lCount As Long
Dim szNames() As String
For lCount = 1 To ThisWorkbook.Sheets.Count
ReDim Preserve szNames(1 To lCount)
szNames(lCount) = ThisWorkbook.Sheets(lCount).Name
Next lCount
ThisWorkbook.Sheets(szNames()).Select
End Sub

However, if you just want to select all of the sheets in a workbook, this
would be a lot easier:

ThisWorkbook.Sheets.Select

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[mrmac]

Thank you Rob. It is a lot simpler than I thought.

FYI. The deal is to copy all charts from a wb to a new
wb, then to copy a subset of this to a new wb. Then close
these wbs, delete most of the charts from the original wb
and create new charts from different data. This is done
12 times in a loop and the number of charts created in
each loop is variable. This is the piece I was missing.
Thanks for your timely help.