No announcement yet.

Use of 'Sheet1.Select'

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

  • #2
    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
      Re: Use of 'Sheet1.Select'

      it shud be



      • #4
        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.