Select an array of worksheets VBA

I am trying to write a macro that will select an array of worksheets.
The number of worksheets may vary from book to book so I tried usin
the following:
Sheets(Array("Sheet (1)", Sheets(Sheets.Count))).Select

but this doesn't work.  Can anyone help me out please:confused

--
Jamuc
-----------------------------------------------------------------------
Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3054
View this thread: http://www.excelforum.com/showthread.php?threadid=52431

0
3/20/2006 2:15:51 PM
excel 39879 articles. 2 followers. Follow

6 Replies
344 Views

Similar Articles

[PageSpeed] 12

Are you trying to select all the sheets?

sheets.select

maybe????

Jamuck wrote:
> 
> I am trying to write a macro that will select an array of worksheets.
> The number of worksheets may vary from book to book so I tried using
> the following:
> Sheets(Array("Sheet (1)", Sheets(Sheets.Count))).Select
> 
> but this doesn't work.  Can anyone help me out please:confused:
> 
> --
> Jamuck
> ------------------------------------------------------------------------
> Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30545
> View this thread: http://www.excelforum.com/showthread.php?threadid=524313

-- 

Dave Peterson
0
petersod (12005)
3/20/2006 2:43:42 PM
Sheets(Array(Sheets(1).name, Sheets(Sheets.Count).name)).Select

Will select 1st and last worksheets

HTH
--
AP

"Jamuck" <Jamuck.24z27a_1142864400.6791@excelforum-nospam.com> a �crit dans
le message de news:Jamuck.24z27a_1142864400.6791@excelforum-nospam.com...
>
> I am trying to write a macro that will select an array of worksheets.
> The number of worksheets may vary from book to book so I tried using
> the following:
> Sheets(Array("Sheet (1)", Sheets(Sheets.Count))).Select
>
> but this doesn't work.  Can anyone help me out please:confused:
>
>
> -- 
> Jamuck
> ------------------------------------------------------------------------
> Jamuck's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=30545
> View this thread: http://www.excelforum.com/showthread.php?threadid=524313
>


0
ardus.petus (319)
3/20/2006 2:44:09 PM
Thanks for that.  Is it possible to select a range of worksheets ie fro
say the second sheet to the last

--
Jamuc
-----------------------------------------------------------------------
Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3054
View this thread: http://www.excelforum.com/showthread.php?threadid=52431

0
3/20/2006 2:58:21 PM
One way:

    Dim iCtr As Long
    With ActiveWorkbook
        For iCtr = 2 To .Sheets.Count
            .Sheets(iCtr).Select Replace:=CBool(iCtr = 2)
        Next iCtr
    End With



Jamuck wrote:
> 
> Thanks for that.  Is it possible to select a range of worksheets ie from
> say the second sheet to the last?
> 
> --
> Jamuck
> ------------------------------------------------------------------------
> Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30545
> View this thread: http://www.excelforum.com/showthread.php?threadid=524313

-- 

Dave Peterson
0
petersod (12005)
3/20/2006 7:10:46 PM
Thanks Dave but I pasted in the code you gave and it returned an error. 
I understand all that you wrote except for the 'replace' statement which
I guess is the key to it.


-- 
Jamuck
------------------------------------------------------------------------
Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30545
View this thread: http://www.excelforum.com/showthread.php?threadid=524313

0
3/24/2006 8:53:24 AM
Replace will specify whether you want the sheet selected by itself or added to
the group of selected sheets.

If it didn't work for you, you'll have to post the code you used and the actual
error you received.

Jamuck wrote:
> 
> Thanks Dave but I pasted in the code you gave and it returned an error.
> I understand all that you wrote except for the 'replace' statement which
> I guess is the key to it.
> 
> --
> Jamuck
> ------------------------------------------------------------------------
> Jamuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30545
> View this thread: http://www.excelforum.com/showthread.php?threadid=524313

-- 

Dave Peterson
0
petersod (12005)
3/24/2006 11:29:48 AM
Reply:

Similar Artilces:

Can't highlight selected item in list control
I can't seem to highlight an item in a list control I've got. The following code, from OnInitDialog, successfully adds an item to the list, and DOES select it, but it doesn't highlight the row immediately. I have to click in the list to trigger the highlight. I don't get it - I thought the "Redraw" call would take care of it. I threw in "UpdateWindow" but that didn't fix it either. Any thoughts? Thanks. // Add item to list List.SetItem(1, 0, LVIF_TEXT, Str, 0, 0, 0, 0); // Select the first row by default List.SetItemState(1, LVIS_SELECTED, LVIS_SELECTE...

Selecting an Excel tab, Delete Worksheet command is not active
In a shared Excel workbook, when I select a tab to delete that worksheet, the Delete Worksheet command in the Edit menu is not available. -- May your computer never glitch . . . this is documented in Excel's help. Look for: Features that are unavailable in shared workbooks for a big list. missyjes wrote: > > In a shared Excel workbook, when I select a tab to delete that worksheet, the > Delete Worksheet command in the Edit menu is not available. > -- > May your computer never glitch . . . -- Dave Peterson ...

Turning off display while VBA running
Seems to me I once saw an option I can turn off to keep Excel from updating the display while my program inserts rows, fills in data, hides/unhides rows and such, so it'll run faster if there's a lot to be accomplished...the idea being that the program will turn the display, so to speak, back on just before it gets to End Sub. Now I want to use that feature and I can't find it in my VBA/Excel chm. Can anyone tell me where to look for it? Thanks. Application.Screenupdating Tim "Bob Bridges" <rhbridg.RemoveThisNode@attglobal.net> wrote in message ...

Increase rows in worksheet beyond 65,536 row limit
How can I increase the number of rows in a worksheet beyond the 65,536 row limit? "Ann Calantzis" <Ann Calantzis@discussions.microsoft.com> wrote in message news:EF5C2C5A-5340-4BAF-91BB-809F32BBE1F3@microsoft.com... > How can I increase the number of rows in a worksheet beyond the 65,536 row > limit? You can't. -- David Biddulph If you truly need that many rows, you're probably in need of a different type of data management software, such as Access.|:>) "Ann Calantzis" wrote: > How can I increase the number of rows in a worksheet beyond th...

Excel 97 Select Behavior
When you open Excel 97 running on Windows 95, you can type in cells and hit enter or tab and all is well. However, as soon as you SELECT a cell by clicking in it, the mouse appears to be in select and drag mode. You cannot click within another cell and as you move the mouse around, adjacent cells are selected. There is an icon of a paper next to a white arrow. The only way Excel and even be closed from here is by using the Task Manager. This makes Excel unusable. Is there anything we can try short of rebuilding the machine? I don't have a clue even what to look for to diagnose the p...

Select an Employee
I need to run a report from a query where I have EmpName, EmpShift, DateComp as fields among others containing information about safety hazards that employee has turned in. So far I have a dialog box set up so that a user can select whether to pull up information about the entire shift or items that were completed within a certain date range. I also want to be able to see everything a certain employee has turned in, but I can't get it to work for some reason. I've tried using parameters, but it doesn't work. I don't know what else to try. Please help! -- Message posted v...

VBA with excel worksheets?
I am new to VBA and am excel user. Can someone tell me: 1 - How to suck a range of excel data cells into vba code? I just want to put data into some cells and have VBA load that data into an array and use it then return the result into a specified cell in a spreadsheet like sheet1 in testfile.xls I thought i use Worksheets("Sheet1". Range Cells(A1:A6) .... But this syntax is not right and i am having trouble searching to find my solution to my problem. Thanks in advance, Andrew I suspect that yhis may be a case of my OE not showing me replies that you have had because I fin...

creating drop down lists where you can select multiple values
Hi there I want to create drop down lists in an excel spreadsheet that allow me to select more than one value to be displayed in the cell. Does anyone know how to help me? Thanks in advance You can do this with programming. There's a sample file here: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'Select Multiple Items from Dropdown List' Angella wrote: > Hi there > > I want to create drop down lists in an excel spreadsheet that allow me to > select more than one value to be displayed in the cell. > > Does anyone know h...

VBA code to launch Email
I am looking for a vba code that when run, will open the email browser insert email addresses into the (To:) and have the workbook i'm runnin the vga code in attached to this email message. Is this possible? -- Et3rna ----------------------------------------------------------------------- Et3rnal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=445 View this thread: http://www.excelforum.com/showthread.php?threadid=26267 Hi see: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany "Et3rnal" <Et3rnal.1d0okp@excelfor...

Excel VBA
Hello All, I'm trying to compare two columns for new entries. Column A is the source information and column B is the coulmn to compare against. If there are new entries in column A I'd like the macro to record these new entries. Just the values would be perfect. For example if I have two columns with; ____A____B 1__105__105 2__106__106 3__107__107 4__108__108 5__109__109 6__111__111 7__112__112 8__113__113 9__114__114 10_115__115 11_116__116 12_117__117 13_118__118 14_119__119 15_120 16_121 I would like my macro to tell me that 120 and 121 are not in the second column. My column...

Can VBA open a form in an already open database?
I am able to use the OpenCurrentDatabse method to open a new instance of an existing database and then open a form within that database. However, if the database I am interested in is already open, is there a way to open a form in the existing instance of that (already open) database? Thanks. NB. The VBA code being executed exists in one database, and the form in a different database. If the database you are opening is not opened in exclusive mode, you should be able to open it. Are you having problems with it? -- Dave Hargis, Microsoft Access MVP "billelev" wrote: > I a...

VBA Editor skips lines and moves to other part of code
Hello, MS ACCESS 2003 on XP PRO. I have a form checkbox, and AFTERUPDATE of the value, I run a bunch of code. Part of the code calls a sub in a module. During this sub, I define two string values. (they happen to be shaped like SQL statements but at this point the computer doesn't know that) the first one runs fine, and the MSGBOX statement after shows the text correctly. However, when the yellow gets on top of the SECOND str_SQL = statement, and I hit F8, the code skips OUT of the SUB, back to the AFTERUPDATE statement on the line that would execute after the sub. T...

ntdll.dll failure after vba Int (random num)
I am getting a system freeze on a specific terminal - the freeze is the result of an ntdll.dll failure but I don't have a clue what's doing this and why this dll would fail. I have tried to come up with the simplest test I could so I have send key Setocus to a button (with no OnClick event) then send key {ENTER} after a random amount of time (well sort of random) between 25 min and (approx 41 mins. I don't think the send keys will cause anything to fail - ?? Me.testbutton.SetFocus Dim RandomNumber As Integer 'txtRandomNumber is an unbound box' me.txtRa...

Filter one combobox based on another combobox selection
I had 2 tables, called ProdInfor1 n ProdInfo2. Both are linked by Product Code. Info1 contains the Product Item and Info2 contains the Size. So I have this form, frmProductDetails which I would like to search for the products using 2 combo boxes, which the first 1 is to select the product that I want and subsequently I will want the 2nd one to filter out the sizes that this Product has. And these 2 combo boxes are to appear in the form itself. I came across 1 good example at http://www.databasedev.co.uk. However, it is not within the form itself. And with that, I realized that the sa...

Query to check a field and select fields from a table
I have 2 tables (tServers and tTimezone) with the following structure: tServers: servername city tTimezone: city time1 time2 My query need to check if [tServers].servername is like "*DOWD*" and return a field "time" populating with [tTimezone].time2. I joined both tables using city. "Rodrigo Borges" <rsborges@hotmail.com> wrote in message news:D955F76E-0CCD-4B16-8E01-B7768189A119@microsoft.com... >I have 2 tables (tServers and tTimezone) with the following structure: > > tServers: > servername > city > > tTimezone: > city >...

how do I copy the worksheet tab name to a cell?
I've got a workbook with 12 tabs. Within one of the tabs, I'd like to create a table of the 12 tab names. When I change a tab name, I want the table to be updated automatically. I posted an example file to http://www.Galimi.com/Examples/shtChange.xls Following is the Workbook_Activate code Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name <> shtPlace.Name Then Exit Sub For Each sht In ThisWorkbook.Sheets shtPlace.Range("nmSht").Offset(intr) = sht.Name intr = intr + 1 Next End Sub http://HelpExcel.com...

Display Print Dialog with Selection: Excel 2003
I am using Application.Dialogs(xlDialogPrint).Show to display the print dialog. How do I activate "Selection" under Print What? -- Colleen Not sure I understand the question, but the Selection option would apply to a range that was selected prior to displaying the dialog box. i.e. If you had selected Range("A1:D10") prior to opening the Print dialog box, you would simply select the "Selection" option and it would print that range. You cannot make the selection after you have opened the dialog box. "Colleen M" <ColleenM@discuss...

VBA code to insert pictures in a column of cells
I have a folder of 99 pictures with filenames: image01.jpg, image02.jpg up to image99.jpg. All of these images are small, and the same size. I want to insert them (in order) in a column of cells (A1, A2, to A99) using VBA. The images should be embedded, not linked. Resizing cells to the right size would be a nice extra, but not necessary - it's easy in this case to resize manually. Thanks for looking at my question - and I hope to hear some good suggestions! ...

Spell Check with protected worksheet
How do you setup that user can use spell check on the unprotected range when the worksheet protection is on? We are putting together an Excel sheet as a form and really need thi feature as well. Did you ever get it to work? Mik -- Mike Drummon ----------------------------------------------------------------------- Mike Drummond's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1683 View this thread: http://www.excelforum.com/showthread.php?threadid=27220 ...

Force a customer selection for a specific product
We have RMS 1.3 installed in our establishments. Is it possible to have a particular product that cannot be sold without first selecting a customer. I know there an options to always select a customer, but we only want the customer selection to be forced when a particular product is sold. Is it possible? Thanks in advance. -- Leo Hi Leo - not out of the box; I am not aware of a way to link the sale of an item to assigning a customer to a transaction; you could create a "customer required" message and link that to the item, to remind the cashier to create/assign a customer ...

Selecting Rows in Pivot Table
I've created a simple Pivot table in excel 2000 which gets data from an access db. The final resulting pivot table has hundreds of rows. The user can uncheck values to limit the rows but that will take hours if he only wants to see data for a few rows (which is usual ). Is there a way the pivot table can start with zero rows ( all row values unchecked ) and let the user select rows by checking them? The other option is to drop the row field on the page field but then the table shows only one record at a time depending on what value is selected from the page field? I hope i've made my...

Select N rows and get Minimum
Dear All I need your help to sove this problem. I have a table Customers_Bal containing the following Information: Date Balance 01/01/2007 1000 01/02/2007 700 01/03/2007 800 01/04//2007 600 01/05/2007 1200 01/06/2007 1800 01/07/2007 2600 01/08/2007 900 01/09/2007 600 I need to select the first N rows and get the min(balance) and put it a new field (Bal_Min) in the Nth record. after select nrows from the second row - Get the min(balnce) put it a new field (Bal_Min) in the Nth+1 record and so on till the EOF. Let's suppose t...

VBA to autofill from relative location
If I need to autofill column B from the last cell that has data in it to the same row as the last cell of column A that has data in it, how do I set the destination for the autofill correctly? I have tried a lot of ways that seemed obvious and all of them return errors. Here is where I am stuck: Selection.AutoFill Destination:=Range(B[this number is the active cell, or the one I just put a value in in column B]:B[this number should be the same as the last cell that has a value in it in column A]) Try Dim lngRow As Long lngRow = Cells(Rows.Count, "A&quo...

go to worksheet on button name
I am trying to assign a macro to a button so that when clicked it opens the worksheet whose name is on the button Copy this event sub in the code window of worksheet containing the button: Private Sub CommandButton1_Click() Worksheets(Me.CommandButton1.Caption).Select End Sub Regards, Stefi „Northern Dave” ezt írta: > I am trying to assign a macro to a button so that when clicked it opens the > worksheet whose name is on the button ...

removing and deleting elements of an array
I have an array of sprite pointers: CArray <CSprite*, CSprite*> m_Sprite; I grow the array in another class: for(int i = 0; i < m_nSpriteCount; i++) { . . . m_Sprite.Add(new CSprite(this, i)); . . . } And now, when I want to remove, say, the 3rd sprite, should I do this? : m_Sprite.RemoveAt(2); delete m_Sprite[2]; No, that would delete what originally was the fourth sprite. Do something like this instead CSprite* p = m_Sprite.GetAt(2); m_Sprite.RemoveAt(2); delete p; "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:OKoB...