Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
please help
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. -----Original Message----- 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" wrote in message ... 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|