Sheet codename not working

Hi,
XP and Excel2003.

What might be the reason why this is not working (Sheet2 is codename):
Sheet2.Select

I have tried all possibilities that I can imaging, but no. I have another 
file and selecting sheet with code name (Sheet4.Select) funtions just fine.

Now using code:
    Workbooks.Open Filename:=Doc2
    ActiveWorkbook.Sheet2.Select

Opens Doc2 excel file just fine, but does not open the sheet.

Thanks in advance
MakeLei
0
Utf
6/1/2010 11:32:02 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1423 Views

Similar Articles

[PageSpeed] 45

Use the index number instead.
Activeworkbook.Sheets(2).select

OR the sheetname itself

Activeworkbook.Sheets("Sheetname").select

-- 
Jacob (MVP - Excel)


"Makelei" wrote:

> Hi,
> XP and Excel2003.
> 
> What might be the reason why this is not working (Sheet2 is codename):
> Sheet2.Select
> 
> I have tried all possibilities that I can imaging, but no. I have another 
> file and selecting sheet with code name (Sheet4.Select) funtions just fine.
> 
> Now using code:
>     Workbooks.Open Filename:=Doc2
>     ActiveWorkbook.Sheet2.Select
> 
> Opens Doc2 excel file just fine, but does not open the sheet.
> 
> Thanks in advance
> MakeLei
0
Utf
6/1/2010 11:58:02 AM
Just use

    Workbooks.Open Filename:=Doc2
    Sheet2.Select

the newly opened workbook will be the active book.

-- 

HTH

Bob

"Makelei" <Makelei@discussions.microsoft.com> wrote in message 
news:66F8E17A-939C-43FA-A391-6FF603483867@microsoft.com...
> Hi,
> XP and Excel2003.
>
> What might be the reason why this is not working (Sheet2 is codename):
> Sheet2.Select
>
> I have tried all possibilities that I can imaging, but no. I have another
> file and selecting sheet with code name (Sheet4.Select) funtions just 
> fine.
>
> Now using code:
>    Workbooks.Open Filename:=Doc2
>    ActiveWorkbook.Sheet2.Select
>
> Opens Doc2 excel file just fine, but does not open the sheet.
>
> Thanks in advance
> MakeLei 


0
Bob
6/1/2010 12:07:46 PM
Oops...I missed the boat completely...

Try (if trusted access to Visual Basic Projects is turned ON)

Activeworkbook.VBProject.VBComponents("Sheet1").Activate 

OR 

Dim ws As Worksheet, strSheetCodeName As String
strSheetCodeName = "Sheet1"
For Each ws In Worksheets
If ws.CodeName = strSheetCodeName Then ws.Select: Exit For
Next

-- 
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

> Use the index number instead.
> Activeworkbook.Sheets(2).select
> 
> OR the sheetname itself
> 
> Activeworkbook.Sheets("Sheetname").select
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Makelei" wrote:
> 
> > Hi,
> > XP and Excel2003.
> > 
> > What might be the reason why this is not working (Sheet2 is codename):
> > Sheet2.Select
> > 
> > I have tried all possibilities that I can imaging, but no. I have another 
> > file and selecting sheet with code name (Sheet4.Select) funtions just fine.
> > 
> > Now using code:
> >     Workbooks.Open Filename:=Doc2
> >     ActiveWorkbook.Sheet2.Select
> > 
> > Opens Doc2 excel file just fine, but does not open the sheet.
> > 
> > Thanks in advance
> > MakeLei
0
Utf
6/1/2010 12:26:08 PM
On Tue, 1 Jun 2010 04:32:02 -0700, Makelei
<Makelei@discussions.microsoft.com> wrote:

>Hi,
>XP and Excel2003.
>
>What might be the reason why this is not working (Sheet2 is codename):
>Sheet2.Select
>
>I have tried all possibilities that I can imaging, but no. I have another 
>file and selecting sheet with code name (Sheet4.Select) funtions just fine.
>
>Now using code:
>    Workbooks.Open Filename:=Doc2
>    ActiveWorkbook.Sheet2.Select
>
>Opens Doc2 excel file just fine, but does not open the sheet.
>
>Thanks in advance
>MakeLei

Probably because ActiveWorkbook<>Doc2

Maybe something like:

Workbooks(Doc2).Worksheets("Sheet2").Activate

0
Ron
6/1/2010 12:29:58 PM
"Jacob Skaria" <JacobSkaria@discussions.microsoft.com> wrote in message 
news:1E2085E6-4398-
>
> Dim ws As Worksheet, strSheetCodeName As String
> strSheetCodeName = "Sheet1"
> For Each ws In Worksheets
> If ws.CodeName = strSheetCodeName Then ws.Select: Exit For
> Next
>
> -- 
> Jacob (MVP - Excel)

Yep, that's the way I'd do it.

Regards,
Peter T 


0
Peter
6/1/2010 7:11:51 PM
Makelei explained on 6/1/2010 :
> Hi,
> XP and Excel2003.
>
> What might be the reason why this is not working (Sheet2 is codename):
> Sheet2.Select
>
> I have tried all possibilities that I can imaging, but no. I have another 
> file and selecting sheet with code name (Sheet4.Select) funtions just fine.
>
> Now using code:
>     Workbooks.Open Filename:=Doc2
>     ActiveWorkbook.Sheet2.Select
>
> Opens Doc2 excel file just fine, but does not open the sheet.
>
> Thanks in advance
> MakeLei

Here's a reusable function I use that returns the Worksheet.Name from 
any workbook given its Worksheet.CodeName.

Function Get_SheetTabName(CodeName As String, Optional Wkb As Workbook) 
As String
  Dim Wks As Worksheet
  If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
  For Each Wks In Wkb.Worksheets
    If Wks.CodeName = CodeName Then _
      Get_SheetTabName = Wks.Name: Exit Function
  Next
End Function

regards,

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
GS
6/1/2010 8:13:28 PM
Reply:

Similar Artilces:

How can I share information between multiple sheets in Excel?
New question: I know now how to use "=sheet1!a1" to copy cells from sheet 1 to sheet 2,3 etc. Is there is a formula that will allow me to enter data into sheet 2, and have it copy automatically into sheet 1 and 3, or into sheet 3 going to sheet 1 and 2? So that I can enter the data into any sheet and have it transfer to all sheets, rather than always from sheet 1 to sheets 2 and 3. Nothing built into excel (except for the formula you showed). But I'd be very hesitant to separate my data. I'd do my best to keep all the data on one worksheet. You can use Data|Fi...

Rules not working #9
On Outlook 2007, I have a rule that works fine looking at the subject. But I cannot get my rule that looks at the message header to work. I only have "With specific words in the message header" checked. I then copied the message directly from the email and pasted it in the Search Text textbox and Added it - "Going to Sleep for 10 Minutes". I then told it to delete it. This is the only thing that this rule does. It won't delete any of the messages with this message in it. I only want it to run when I tell it to as I do my other message but it won't seem to d...

Call from a different sheet?
I'm wondering if it's possible in Excel to call data from a different sheet. For example if I changed A1 in sheet 2 it'd affect forumlas in sheet 1. -- HisHighness ------------------------------------------------------------------------ HisHighness's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31454 View this thread: http://www.excelforum.com/showthread.php?threadid=511552 You can link to another worksheet with a formula like ='Sheet1'!A1 -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson...

Transfer the total of mutiple entries in a sheet to another sheet in the same file #2
How to transfer the total of multiple of multiple entries in a sheet to another shet in the same file. Please help me in this formula ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** ...

Why doesnt this work
Dear All, Can you have a look at this and please tell why it doesnt work Sub CopySheetAsHTML() Dim fName As String Dim wks As Worksheet Set wks = ActiveSheet fName = wks.Range("J4").Value With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _ "I:\" & fName & ".htm", wks.Name, "DataRange", _ xlHtmlStatic, wks.Range("J4").Value) .Publish (True) End With End Sub Many Thanks in advance Steve Where to start? 1. Do you have this code in a standard Excel module? 2. Do you have a worksheet (not a chart sheet) a...

can outlook work without joining local domain?
we have situation where client wants to use exchange server 2003 without having workstations to join domain. Can this work? Thank you in advance. arun Yes, it works. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "arun mx" <arunmx@discussions.microsoft.com> wrote in message news:7EB88EC5-3ACF-45CF-B14C-B75E44604872@microsoft.com... > we have situation where client wants to use exchange server 2003 without > having workstations to join domain. Can this work? > Thank you...

Customer charge Accounts don't work.
We just upgraded from Quicksell 2000 and everything transfered great. The charge accounts transfered but we can not add new charges to them. The error we get is "the charge has no slip receipt". Can't figure out how to set that up to create charges. Can anybody lend a hand. Beaver, Are you trying to use a QS2K receipt template? If so, try an RMS one. I'm assuming you have created an Account type for your charge customers and a Payment type for charge accounts. As a test, create a new customer, Account tab, change the Account type to your charge account, give ...

HOW DO I CHANGE THE ORDER OF THE SHEETS IN EXCEL WORKBOOK?
my sheets are not in the order I want them. How do I change the order. The data is already in the sheets. Click on the tab and drag it to where you want to. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------�------------------------------�---------------- "joanbaer" <joanbaer@discussions.microsoft.com> wrote in message news:1F45...

Max number sheets in a file?
How many sheets can an excel 2002 file have if each sheet has only abou a page of text? I'm just looking for a general idea, if it's lots an lots and I don't need to worry about it that's a good enough answer fo m -- Message posted from http://www.ExcelForum.com The number will be limited by the memory in your computer. Certainly hundreds of sheets is well within most machine's capacity. In article <bishopwill.154u1g@excelforum-nospam.com>, bishopwill <<bishopwill.154u1g@excelforum-nospam.com>> wrote: > How many sheets can an excel 2002 file hav...

combine work and personal folders in outlook today
Hi, I have 2 seperate Outlook files, one covers the exchange server at work through our VPN and one covers my Gmail account and personal calender entries etc. I'm wondering if it's possible to combine the 2 accounts so in Outlook today it will for example in the calender section mention both upcoming work entries and also personal ones. Thanks in advance for any help! -Dan daniel.byrne@gmail.com <daniel.byrne@gmail.com> wrote: > I have 2 seperate Outlook files, one covers the exchange server at > work through our VPN and one covers my Gmail account and personal > ca...

Update Automatic Links Not Working in 2003
When opening a file and being asked "Update Automatic Links?", I respond yes but the links don't update. If I go into Edit, Links and update manually, then it works. The original file was created in Excel 97 or 2000; the feature does not work on users who have 2003; the feature does work on users who have 97 or 2000????? I've seen this, too, and found that I have to press F9 to recalculate the spreadsheet. Thanks for the tip but F9 did not do anything. "Dave O" wrote: > I've seen this, too, and found that I have to press F9 to recalculate > the sp...

Sheet to sheet copying
Hi, I have two spreadsheets. One called nursing and the other medical. I want to be able to copy certain cells from one sheet to other and visa verca without the need to copy & paste. So I want a cell in nursing to search a column for specific text in the medical sheet, then I want the contents of cells in the row where the text is found to be copied to cells in the nursing sheet. Example: The cells B3-G3 in the nursing sheet to search Column A in the medical sheet for specific text e.g. "G1". When "G1" is found in cell A5 then the contents of cells B5-G5 in t...

Check Register to Mortgage Sheet
Hi; I am using MS Money 2007. If I write a check in my register to my mortgage company, how do I get that transaction to update my mortgage account register? Do I have to type it in twice (Once to my check register and once to my mortgage register)? I would like to set some stuff up where items in my check register automatically cary to other account items. Thanks for any advice on this. Pat From the Check Register make it a transfer to your Mortgage Account. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mn...

list sheet comments
Hi, I have a sheet with a lot of comments. I would like to create a macro that lists all the comments of the sheet in column F. I=B4ve tried something similar like this: For Each cmt in ActiveSheet.Comments MsgBox cmt.Text Next cmt But I don=B4t know how to list each comment in each cell of column F instead of a msgbox. thanks Dim StartCell as Range dim Cmt as comment with activesheet set startcell = .range("F1") for each cmt in .comments startcell.value = cmt.txt 'come down to the next cell set startcell = startcell.offset(1,0) n...

Money has stopped working
I just purchased Money Plus Premium and whenever I go into investments I get the following error: "Microsoft Money has stopped working". I implemented article # 942485 and it had no impact on my issue. This is fairly aggrevating, any help is appreciated... -- Thank you! Operating System is? (I don't know MSKBs by number without URLs--is this the one on Vista DEP?) "i005860" <osterhaus@msn.com> wrote in message news:33BE6872-EEA6-47BA-A07E-024BF6DCCFAF@microsoft.com... > I just purchased Money Plus Premium and whenever I go into investments I > g...

Email forwarding not working for external addresses
I'm administering an Exchange 2003 environment that was recently migrated from an Exchange 2000 environment. We moved the mailboxes from the 2k server to the 2k3 server using the 2k3 Move Mailboxes utility. We have several user accounts that are forwarded to email addresses outside the domain, both using contacts and by associating an external email address with the user account (by using Exchange Tasks...-->Establish Email Addresses in ADUC). All forwarding works fine when sending from an email address within the domain. If someone sends an email from a non-domain address to an acc...

blank out rest of work sheet
I was wondering how to blank out the empy cells in my worksheet. For example: I have data in columns A-F down about 50 rows. I do not want to see any rows past that. When I am saving the data as a text file, I am getting extra rows in my file for some reason and I want to see if this would prevent this from happening. Thanks... --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.769 / Virus Database: 516 - Release Date: 9/24/2004 Hi Shawn, See the explanation and remedial notes on Debra Dalgleish's web site: ...

How to populate one sheet with data from another sheet
I have 2 sheets in the excel file. On the first sheet the first colum is a part num only. The second sheet has part num on the first column and descriptions on the second column. I need a macro or script that will scan the second sheet for part num that matches the first sheet part num and populate the description(at the second column) on the first excel sheet. -- KH One way .. Assuming the reference data is in Sheet2, cols A and B col A = part#, col B = description In Sheet1 ----------- With col A containing part# in A2 down, Put in B2: =IF(ISNA(MATCH(A2,Sheet2!A:A,0)),"&quo...

No Bound Sheets Found Error Dialog Box-Excel 2000
Hello all. I have a 8.5MB spreadsheet that is the summary of weekly timesheets that are Excel files. I have attempted various strategies to reduce the size of the files. This sheet is all references to other worksheets. Up until two days ago, when opening the file and it asks, this contains references to other files...do you want Excel to update the cell refences, y or N. I always hit okay and it does it's stuff. Now however, when the box comes up when opening the file, I hit okay and it returns a "No Bound Sheets" error dialog box. All I can do is hit okay and a...

Changing from sheet to sheet
Hi there! Is there a function that would enable me to navigate from sheet to sheet in an Excel document (instead of using the mouse and click on the sheet I want to work with)? Many thanks! Hi Ctrl PgUp and Ctrl PgDwn is one way. Insert > Hyperlink (and after that clicking it) is another. HTH. Best wishes Harald "Greywolf" <anonymous@discussions.microsoft.com> skrev i melding news:5bfc01c42d6b$c2a26630$a101280a@phx.gbl... > Hi there! > > Is there a function that would enable me to navigate from > sheet to sheet in an Excel document (instead of using the &...

Change password from desktop does not work 08-25-10
Hi, Whenever my users try to change their password (ctrl+alt+del > change password) they are told that their password does not meet the policy requirements - which is not true. I have tried this on my Vista desktop and at first it worked. I then tried it again and it wouldn't work, and the second password was much more complex than the first attempt. So, I tried to reset my password using the SBS Console to this second password that was rejected on the grounds of not meeting the complexity policy requirements and it worked. So obviously this second password was fine, but i...

Cross apply from function not working
I have the following function: *************************************** USE [Northwind] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create FUNCTION [dbo].[fnTops] (@Top int, @CategoryID int) RETURNS TABLE AS RETURN SELECT TOP(@Top) ProductName, UnitPrice FROM dbo.Products WHERE CategoryID=@CategoryID ORDER BY UnitPrice DESC; ***************************************** If I do: select * from [dbo].[fnTops](3,5) It returns 3 rows fine. If I do: SELECT CategoryID, CategoryName, ProductName, UnitPrice FROM dbo.Categories C CROSS AP...

Delete values in sheet 2 that arre found in sheet 1
Is there an easy way to delete values in sheet 2 that match the value from sheet 1. I have approximately 10000 rows in sheet 1 and some identical rows on sheet 2 that need to be eliminated? I not sure if this has to be done pragmatically or if there is some feature in excel 2007 that can handle this type of request. thanks Nick If it is the values in a single column on each sheet that make up the duplication, it's fairly easy. Lets say you want to compare the contents of column A on each sheet to find duplicates on Sheet2 (that are also on Sheet1). Your data starts o...

Media player not working
I am having trouble with the Media Player not showing the videos that people send me in their emails. When I download , the Media player opens , and appears to be working yet I get no picture or voice. Can anyone out there help me resolve this problem? Remember I am a novice at computer stuff! Thanks -- Paul Jay What version of WMP and Windows? What's the file type of these videos (AVI, WMV...)? What e-mail client are you using? If you save the videos to the hard drive first, and then try to play the saved video, does that work? Regards -- Tim De Baets http://...

Function to Copy all sheets into one sheet
Is there a way to copy multiple sheets at once to create one worksheet, rather than one at a time? You could use a for/each loop for each ws in worksheets depends on what and where you want to copy next -- Don Guillett SalesAid Software donaldb@281.com "Des" <cusaty@express-scripts.com> wrote in message news:013801c3b43a$5cb403b0$a101280a@phx.gbl... > Is there a way to copy multiple sheets at once to create > one worksheet, rather than one at a time? ...