Error 1004 using array in Sheet select.

If I select a sheet with its real name:

 Sheets("Sheet1").Select                             >>>>>- Works fine
 Sheets(Array("Sheet1", "Sheet2")).Select    >>>>>- Works fine

If I assign the sheet name(s) to a constant

Public Const TSGSheet1 = "Sheet1"
Public Const TSGSheet2 = "Sheet2"

 Sheets(TSGSheet1).Select    >>>>>- Works fine
 Sheets(TSGSheet2).Select    >>>>>- Works fine

Sheets(Array(TSGSheet1, TSGSheet2)).Select  >>>>>- fails with Error 1004

My macro's/VBA do multiple Selects and rather than explicitly specifying the 
sheet names in every command, I thought I would set up a constant, use the 
constant in the multiple commands. If I then wanted to change the sheet name, 
I could just change the constant assignment once.

Have I done something wrong? It would appear that you can not use constants 
in an array?

-- 
Trefor
0
Trefor (15)
9/7/2005 4:22:06 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
694 Views

Similar Articles

[PageSpeed] 53

Trefor,

It works for me and selects the two sheets, 
if the public constants are placed at the top of the module above all the subs.

Jim Cone
San Francisco, USA


"Trefor" <Trefor@home.com> 
wrote in message 
news:B6435890-15AA-4182-A80A-3EEBDE097494@microsoft.com
If I select a sheet with its real name:

 Sheets("Sheet1").Select                             >>>>>- Works fine
 Sheets(Array("Sheet1", "Sheet2")).Select    >>>>>- Works fine

If I assign the sheet name(s) to a constant
Public Const TSGSheet1 = "Sheet1"
Public Const TSGSheet2 = "Sheet2"
 Sheets(TSGSheet1).Select    >>>>>- Works fine
 Sheets(TSGSheet2).Select    >>>>>- Works fine
Sheets(Array(TSGSheet1, TSGSheet2)).Select  >>>>>- fails with Error 1004

My macro's/VBA do multiple Selects and rather than explicitly specifying the 
sheet names in every command, I thought I would set up a constant, use the 
constant in the multiple commands. If I then wanted to change the sheet name, 
I could just change the constant assignment once.
Have I done something wrong? It would appear that you can not use constants 
in an array?
-- 
Trefor
0
jim.coneXXX (771)
9/7/2005 5:09:49 PM
And it worked for me, too.

Are you sure that the active workbook had sheets with those two names?


Trefor wrote:
> 
> If I select a sheet with its real name:
> 
>  Sheets("Sheet1").Select                             >>>>>- Works fine
>  Sheets(Array("Sheet1", "Sheet2")).Select    >>>>>- Works fine
> 
> If I assign the sheet name(s) to a constant
> 
> Public Const TSGSheet1 = "Sheet1"
> Public Const TSGSheet2 = "Sheet2"
> 
>  Sheets(TSGSheet1).Select    >>>>>- Works fine
>  Sheets(TSGSheet2).Select    >>>>>- Works fine
> 
> Sheets(Array(TSGSheet1, TSGSheet2)).Select  >>>>>- fails with Error 1004
> 
> My macro's/VBA do multiple Selects and rather than explicitly specifying the
> sheet names in every command, I thought I would set up a constant, use the
> constant in the multiple commands. If I then wanted to change the sheet name,
> I could just change the constant assignment once.
> 
> Have I done something wrong? It would appear that you can not use constants
> in an array?
> 
> --
> Trefor

-- 

Dave Peterson
0
petersod (12004)
9/7/2005 5:54:40 PM
Sorry misled you a bit.

Try the following (same constants as before)

    Sheets(TSGSheet1).Visible = True
    Sheets(TSGSheet2).Visible = True
    
    Sheets(Array(TSGSheet1, TSGSheet2)).Visible = False
    
    Sheets(Array(TSGSheet1, TSGSheet2)).Visible = True    <<<<< This fails

The line in my original question failed because one sheet was hidden. The 
last line above fails with the same error.
-- 
Trefor


"Dave Peterson" wrote:

> And it worked for me, too.
> 
> Are you sure that the active workbook had sheets with those two names?
> 
> 
> Trefor wrote:
> > 
> > If I select a sheet with its real name:
> > 
> >  Sheets("Sheet1").Select                             >>>>>- Works fine
> >  Sheets(Array("Sheet1", "Sheet2")).Select    >>>>>- Works fine
> > 
> > If I assign the sheet name(s) to a constant
> > 
> > Public Const TSGSheet1 = "Sheet1"
> > Public Const TSGSheet2 = "Sheet2"
> > 
> >  Sheets(TSGSheet1).Select    >>>>>- Works fine
> >  Sheets(TSGSheet2).Select    >>>>>- Works fine
> > 
> > Sheets(Array(TSGSheet1, TSGSheet2)).Select  >>>>>- fails with Error 1004
> > 
> > My macro's/VBA do multiple Selects and rather than explicitly specifying the
> > sheet names in every command, I thought I would set up a constant, use the
> > constant in the multiple commands. If I then wanted to change the sheet name,
> > I could just change the constant assignment once.
> > 
> > Have I done something wrong? It would appear that you can not use constants
> > in an array?
> > 
> > --
> > Trefor
> 
> -- 
> 
> Dave Peterson
> 
0
Trefor (15)
9/7/2005 6:12:03 PM
Yep.

I think you'll want to do one sheet at a time.

Trefor wrote:
> 
> Sorry misled you a bit.
> 
> Try the following (same constants as before)
> 
>     Sheets(TSGSheet1).Visible = True
>     Sheets(TSGSheet2).Visible = True
> 
>     Sheets(Array(TSGSheet1, TSGSheet2)).Visible = False
> 
>     Sheets(Array(TSGSheet1, TSGSheet2)).Visible = True    <<<<< This fails
> 
> The line in my original question failed because one sheet was hidden. The
> last line above fails with the same error.
> --
> Trefor
> 
> "Dave Peterson" wrote:
> 
> > And it worked for me, too.
> >
> > Are you sure that the active workbook had sheets with those two names?
> >
> >
> > Trefor wrote:
> > >
> > > If I select a sheet with its real name:
> > >
> > >  Sheets("Sheet1").Select                             >>>>>- Works fine
> > >  Sheets(Array("Sheet1", "Sheet2")).Select    >>>>>- Works fine
> > >
> > > If I assign the sheet name(s) to a constant
> > >
> > > Public Const TSGSheet1 = "Sheet1"
> > > Public Const TSGSheet2 = "Sheet2"
> > >
> > >  Sheets(TSGSheet1).Select    >>>>>- Works fine
> > >  Sheets(TSGSheet2).Select    >>>>>- Works fine
> > >
> > > Sheets(Array(TSGSheet1, TSGSheet2)).Select  >>>>>- fails with Error 1004
> > >
> > > My macro's/VBA do multiple Selects and rather than explicitly specifying the
> > > sheet names in every command, I thought I would set up a constant, use the
> > > constant in the multiple commands. If I then wanted to change the sheet name,
> > > I could just change the constant assignment once.
> > >
> > > Have I done something wrong? It would appear that you can not use constants
> > > in an array?
> > >
> > > --
> > > Trefor
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
petersod (12004)
9/7/2005 6:29:56 PM
Reply:

Similar Artilces:

R squared not the same in graph and spread sheet.
I have an X-Y plot displayed with a trend line equation and R squared. I also calculate R squared (RSQ) and the numbers are not the same; 0.8918 and 0.928. Can someone explain this (Excel 2010 and Windows 7)? -- Jim Silverton Extraneous "not" in Reply To. "James Silverton" <jim.silverton@verizon.net> wrote: > I have an X-Y plot displayed with a trend line equation > and R squared. I also calculate R squared (RSQ) and the > numbers are not the same; 0.8918 and 0.928. Can someone > explain this (Excel 2010 and Windows 7)? You would need to post [1] m...

Using iterations to calculate circular references.
I am working with data that requires using circular references in some calculations. I checked the "iterations" box to allow for these calculations to work, and Ecxel seems to be calculating everything correctly. The problem is that after I close the workbook, when I later reopen it to continue working, all the cells that are a part of the circular reference display the #VALUE! error. I can fix this by deleting one cell that is part of the loop and then undoing that command, or by copying the formula from a cell, deleting it, and pasting it back into the same cell. I have to d...

Microsoft CRM using Firefox
Hello, Would anyone by chance know if CRM will work correctly on Firefox. Currently, I have employees that are using Firefox and are encountering the same issue as IE [Internet Explorer bombs out and states that it has encountered problems...]. I am just having employees use IE to prevent problem. Thank you for your comments in advance. Install a Firefox extension called IETab "Marcos" <Marcos@discussions.microsoft.com> wrote in message news:19B93E29-757D-46FF-B747-A17EB112ED94@microsoft.com... > Hello, > > Would anyone by chance know if CRM will work cor...

error sending from 5.5 to 2000
When a user sends from an exchange 5.5 mailbox to a user in exchange 2000, they receive the following error: The message was undeliverable because the recipient specified in the recipient postal address was not known at this address The MTS-ID of the original message is: c=US;a= ;p=Express-Scripts;l=TEMMS014-041001192533Z-210060 <mail5.express-scripts.com #5.1.1> The same user on the 5.5 server can send to other users on the 2000 server. I looked the error up in technet and got 3 kb articles with only 1, kb295377 being anything close to my problem. I checked the entr...

When launching Outlook I recieve the following error... please he
When launching Outlook 2003 I recieve the following error: The add-in "C:\PROGRAM~1\SPAMBL~1\Bin\461~1.0\SBOLExt.dll" could not be installed or loaded. This problem may be resolved by using Detect and Repair on the Help menu. I ran the Detect and Repair and that didn't work. I ran the repair option from Add and Remove Program in Control Panel and that didn't work. I also un-installed and then re-installed Outlook 2003 and that didn't work. I'm running Windows 2000. Any ideals...? Did you have something possibly called SpamBlocker installed at some point?...

Money 2006 file repair application error
I cannot delete an old account because I get a "file may be courrupt" error so I am trying to repair my 2006 file. Following the deirections, when the progress bar almost completes, I get an application exception and send an error report to MS. My event log has 2 app errors 1) Event Type: Error Event Source: Application Error Event Category: None Event ID: 1001 Date: 4/12/2006 Time: 10:47:26 PM User: N/A Computer: xxx Description: Fault bucket 02266399. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 42 75 63 6b 65 ...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Creating a template using Publisher
I need help creating a neighborhood directory template using Publisher and merging an Excel spreadsheet into it. It keeps printing multiple pages of the first page after I merge. What version Publisher? If you have 2003 use the catalog merge for your directory. http://office.microsoft.com/en-us/assistance/CH010504381033.aspx Otherwise you need to setup your page as though it is labels. Determine the size of the area you need for each entry, select labels in page setup, type the size you determined in the page setup. Adjust the gaps and margins when you are ready to print (2000 and bel...

iPhone Email to Entourage Using Exchange
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When I answer an email on my iPhone, it does not register it in Entourage as answered. This function worked fine until the business I work for put everyone on Exchange. Is there a setting for this that I can change? <br><br>Thanks! ...

Error #2950
Hi, I have three tables from which data I use to create a single table. The data is entered into the three tables via a form. I have a macro set up to create/update the single table through some make table queries and update queries of data within the first three tables. I have this macro tied a close form event and I keep getting a 2950 error when in a multiuser environment however the macro works in a single user envioronment. I only really need to have the single table updated daily. Do you have any good tips on error handling procedures so I can skip running of the qu...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

Unable to repy using word editor
When repyling to an e-mail I receive the following message, "This form requires Word as your e-mail editor, but Word is either busy, or cannot be found. The form will be opened in the Outlook editor instead". I found article 284900, that states you may get this message when you have office 2000 and office xp installed on the same computer. This is not the case on my computer, Office 2000 does not exist, only office xp. Can anyone help me? There appear to be a whole host of causes for this error message. Most have no solution. Here are a few that do: http://support.microsoft...

is DCOM error 0x00080012 (Not all the requested interfaces were available) not an error ?
Im checking the return value of a DCOM call by using the macro: FAILED(HRESULT). One particular function call returns the error 0x00080012 (Not all the requested interfaces were available) if the DCOM service is down. Calling FAILED with this HRESULT returns false, meaning that it isnt an error! I think it should be reported as an error as my program cannot call ANY interfaaces beyond this point and crashes. The FAILED macro checks the HR to see if it is negative and 0x00080012 is positive!? Any suggestions? Thanks Andy ...

Exchg2003 First Storage Error
Exchange Enterprise 2003, SP1 on Win2003 - When I attempt to view the mailbox information store within First Storage Group I get an error (Id no. 80040d1b). The only ref. to this error I have found is: http://support.microsoft.com/default.aspx?scid=kb;en-us;817309 - checked my registry & server - they match; registry looks fine. I also am getting Veritas Exchg Info Store errors - "database corrupted". I ran the Eseutil defrag - ran successful, but still can't see mailboxes. I found MS tech net info that suggests re-installing Exchange (to repair): http://www.micr...

Error saving a Shared Workbook
I have a user using a shared workbook and gets an error everytime she does this ONE specific change. She deletes a line and then saves and gets a message that says, "EXCEL.exe has generated errors and will be shutdown by Windows... blah blah blah". If she makes anyother type of changes it saves just fine. I also had another user do the SAME change and that person also got that error message. When the file is changed to "Not Shared" then the line is deleted it saves fine also. The problem is obviously with the file itself and that is shared out. can anybody ...

Is anybody using Symantec Enterprise Vault?
Hi, A vendor has recommended this solution to us, I have looked at the datasheet for the product and it speaks the right language but I was wondering if anybody on here uses it in a live environment and what their opinion of it is. Cheers Sean ...

Operating a small passenger service
I am hoping someone has managed to customise outlook in a way that allows you to use the programe to manage bookings on a small passenger service. I.e input a new booking, number of people, location etc. Then email the days bookings to the different drivers. Maybe it could also be set up to know how many seats are avialale to reduce the risk of double booking? Im using Outlook 2003. ...

Outlook error message #4
Microsoft Visual C++ Runtime Error. What's the problem? ...

Unicode "private use" glyphs in Powerpoint and Word 2008 #2
Office 12.0.0.1 on Leopard 10.5.2, all updates applied to both. A friend is migrating from Windows to Mac, and has come across something that we're trying to work around. He has some .ppt files with font glyphs in from a Unicode .ttf font (a specific purpose font for showing the LCD display characters on a piece of monitoring equipment). When we bring the font and .ppt over to the Mac, the glyphs from the normal ASCII range come over into Powerpoint 2008 fine, but the glyphs from the "private use" range from F000 upwards a way don't transfer. In Powerpoint they show as spac...

Error Code 80070424 12-21-09
I keep getting this Windows update error every time I boot my PC. In the window, it says Windows can't check for updates. Does anyone have any ideas? Recently I installed BitTorrent so could that be my problem? "Norm" <Norm@discussions.microsoft.com> wrote in message news:D2E5CF0C-D31B-4034-B4A8-A8204763A623@microsoft.com... > I keep getting this Windows update error every time I boot my PC. In the > window, it says Windows can't check for updates. Does anyone have any > ideas? > > Recently I installed BitTorrent so could that be my proble...

using dates Part 2
Karl was great in helping me get to this point with dates, now I'm wondering if we can take it 1 step further? For Activity Dates prior to 2/1/2007 they are using a normal reporting year and the formulas below take care of Activity dates >2/1/2007? So for example prior to 2/1/2007 1/1/2006 would have a B_Qtr of 2006-1 1) B_Qtr - 2011-1 --- Format(DateAdd("m",11,[ActivityDate]), "yyyy - q") 2) Year - 2011 ---- Year(DateAdd("m",11,[ActivityDate])) 3) Qtr - Q1 ---- Format(DateAdd("m",11,[ActivityDate]), "q") -- Than...

Installation errors. 01-09-04
I'm setting up CRM 1.2 on a completely clean test environment of windows 2000 servers. Near the end of the installation a message pops up "Setup was unable to register the security service." What could be causing this The usual story: To get the MCCRMSecurityService starting, have you tried re-registering it as follows: On your crm server, at command prompt, go to program files - microsoft crm - bin, then run these commands crmsecurityservice -u crmsecurityservice -r -s Peter Lynch "J. Bridger" <Bridger@nospam.ofllc.com> wrote in message news:0aa001c3d6...

linking sheets
Hi All, How do i link my drop down list in a13 sheet 1 to column f in sheet 2. Hi Jinx if using Data Validation to create your drop down list the easiest way is to range name column F in sheet 2 (go to sheet 2, click on F, click in name box (left hand side of formula bar) type name (no spaces), press enter) then click in Sheet1!A13, choose Data / Validation, choose List - click IN the big white box, press you F3 key to bring up list of names in workbook, choose the name, click OK, click OK again and it should work fine. Hope this helps Cheers JulieD "Jinx" <anonymous@discus...

trouble using signature picker
The signature picker is ghosted out does anyone know how to correct this ? Signature picker was working earlier. Thanks ...

Error in Code
Following is some VBS code I have in my Custom Form developed in Outlook 2003. Line 61 is giving me an error stating: Object doesn't support this property or method: 'Item.GetInspector.ModifiedFormPages' Line No: 61 If you look Line 51 is the same method and it works fine. The funny thing is when I run the code, I get the error, I continue on to the Form, and the code after it works. The code that follows line 61 is dependent on line 61 as evident by if I ' comment it out, I get a separate error asking for objPage2. The code also works if I change the value of one of...