Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    ...instead of:

    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

    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

    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.


    Check out our new reputation system. Click on the "star" under the post!

    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

    Re: Use of 'Sheet1.Select'

    it shud be


    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 General
    Replies: 1
    Last Post: September 27th, 2006, 20:14
    By ilyaskazi in forum Excel General
    Replies: 4
    Last Post: March 17th, 2006, 13:41
  3. A1 in sheet1 = =SUM('sheet2:sheet3'!A1)
    By minrufeng in forum Excel General
    Replies: 4
    Last Post: February 23rd, 2006, 04:01
  4. Sheet1.Activate vs Sheet1.Select
    By mikeburg in forum Excel General
    Replies: 5
    Last Post: October 13th, 2005, 11:29
  5. Changes updated in a sheet1
    By erin64 in forum Excel General
    Replies: 4
    Last Post: February 3rd, 2005, 02:26


Posting Permissions

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