Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing sheets with VBA (Excel 2000)

    I have a worksheet with 3 sheets. I wish to be able to reference them as the 1st sheet / 2nd sheet etc. as the sheet names themselves are constantly changed.

    I basically wish to copy data from the 2nd sheet to the 1st using VBA.

    I know I can use range names to get round this problem but I don't want to do it that way.

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with VBA (Excel 2000)

    If you reference the sheet by index number, it doesn't matter what the sheet name is. For example,

    <pre>Sheets(1).Range("A1")
    </pre>


    will always refer to A1 on the first sheet, regardless of what sheet that is. One caution: Sheets(n) is generic, not specific to Worksheets. That is, if the first sheet in the workbook is a chart, the reference to A1 will fail. If this is a possibility, use

    <pre>Worksheets(1).Range("A1")
    </pre>


    instead.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with VBA (Excel 2000)

    Jim showed you how to reference sheets by their index number. Index number 1 will always reference the sheet on the first tab, no matter which sheet that is. You can also reference sheets by their code name. The code name is the name that shows in the project explorer as not in parenthesis. So, you can reference cell A1 on Sheet1 like this:

    <pre> Sheet1.Range("A1")
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with VBA (Excel 2000)

    Thanks to both of you, I shall be using this simple piece of code.

    Legare - you're my beacon of light!!!

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with VBA (Excel 2000)

    Using Sheets(1) can be confusing, since there are different kinds of sheets:
    - dialog sheets
    - Chart sheets
    - Worksheets

    It is therefor better to use:

    Worksheets(1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing sheets with VBA (Excel 2000)

    Yes, I agree!

Posting Permissions

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