Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Use of 'Sheet1.Select'

  1. #1
    Greychild Guest

    Use of 'Sheet1.Select'

    I've read on this forum before that it's possible to use expressions such as:

    VB:
    Sheet1.Select 
    
    
    ...instead of:

    VB:
    Sheets("Sheet1").Select 
    
    
    The first of these has the advantage in that it is independent of the name of the sheet. It simply refers to the first sheet created in that workbook and, if deleted, it cannot be recreated. However, I seem to encounter errors whenever I try and use this approach. Does anyone have a working example I could look at?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,548

    Re: Use of 'Sheet1.Select'

    Well, there is a couple of ways to do this. THe first way is to create an object reference to sheet 1 (or sheet 2 etc.). The other way is to just use the index references for the sheet. For example

    VB:
    Public Sub test() 
         
        Dim Sheet2 As Sheets 
         
         'create object reference to sheet 2
        Set Sheets2 = Worksheets(2) 
         'Set Sheets2 = Worksheets("Sheet2")  would also work
         
        Sheets2.Select 'you dont need to select the sheet
        Sheets2.Range("A1").Value = "Test" 
         
         'or this - refer to sheets number 2.
         
        Sheets(2).Range("A1").Value = "Test" 
         
    End Sub 
    
    
    Note - you dont need to select the sheet to put values into a cell.

    HTH
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  3. #3
    Join Date
    26th July 2004
    Posts
    1,369

    Re: Use of 'Sheet1.Select'

    it shud be

    VB:
    Sheets(1).Select 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Greychild Guest

    Re: Use of 'Sheet1.Select'

    Hmm - neither of those is quite what I mean. When you look in the Visual Basic Editor in the 'Microsoft Excel Objects' part, it will usually show something like the following:

    Sheet2 (January)
    Sheet3 (February)
    Sheet4 (March)

    ...where the months are the names of each sheet. In the above example, at some stage the first sheet in the workbook has been deleted, so there is no Sheet1. It is not possible to rename / recreate Sheet1 either, as it is an unalterable property.

    I've been told that it's possible to refer to sheets by this unalterable name, but I cannot get it to work.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Mailmerge To Automatically Select Sheet1 Of Worksheet
    By jamesparker_1 in forum EXCEL HELP
    Replies: 1
    Last Post: September 27th, 2006, 20:14
  2. COMPARE SHEET1 with SHEET2
    By ilyaskazi in forum EXCEL HELP
    Replies: 4
    Last Post: March 17th, 2006, 12:41
  3. A1 in sheet1 = =SUM('sheet2:sheet3'!A1)
    By minrufeng in forum EXCEL HELP
    Replies: 4
    Last Post: February 23rd, 2006, 03:01
  4. Sheet1.Activate vs Sheet1.Select
    By mikeburg in forum EXCEL HELP
    Replies: 5
    Last Post: October 13th, 2005, 11:29
  5. Changes updated in a sheet1
    By erin64 in forum EXCEL HELP
    Replies: 4
    Last Post: February 3rd, 2005, 01:26

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno