Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Sheet.Activate versus Sheet.Select

This is a discussion on Sheet.Activate versus Sheet.Select within the Excel Questions forums, part of the Question Forums category; I can't find a good answer via searching (maybe I'm asking the wrong way). When switching from one worsheet to ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Sheet.Activate versus Sheet.Select

    I can't find a good answer via searching (maybe I'm asking the wrong way).

    When switching from one worsheet to another worksheet (within the same workbook), which is the preferred/correct way to switch,
    Sheets("sheet name").Select or
    Sheets("sheet name").Activate?

    I am using Excel 2007.

    Thanks,

    Shawn

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,940

    Default Re: Sheet.Activate versus Sheet.Select

    "Neither" is prefered. The best would be to avoid selecting sheets or ranges whenever possible.

    If it is unavoidable, there is no difference in performance.
    My practice is to use Activate with worksheets and workbooks, and Select with ranges.

  3. #3
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Re: Sheet.Activate versus Sheet.Select

    Thanks.
    If you have to, for example, copy a range of data from sheet 1 to sheet 2, how would you do that without Selecting or Activating sheet 2? Hereís how I have it now:
    ActiveSheet.Range(B2:E10).Copy
    Sheets("Sheet 2").Activate
    Range("B2").Select
    ActiveSheet.Paste

    Shawn

  4. #4
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,115

    Default Re: Sheet.Activate versus Sheet.Select

    That can be shortened to:

    Sheets("Sheet1").Range("B2:E10").Copy Destination:=Sheets("Sheet2").Range("B2")

    Or if you had to do a PasteSpecial instad of a paste:

    Sheets("Sheet1").Range("B2:E10").Copy
    Sheets("Sheet2").Range("B2").PasteSpecial
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  5. #5
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Re: Sheet.Activate versus Sheet.Select

    Aaaaahhh, I see; nice. And that eliminates activating/selecting the second sheet like Mike stated.

    Thanks to both of you.

    Shawn

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,680

    Default Re: Sheet.Activate versus Sheet.Select

    Quote Originally Posted by shawnrpg View Post
    When switching from one worsheet to another worksheet (within the same workbook), which is the preferred/correct way to switch,
    Sheets("sheet name").Select or
    Sheets("sheet name").Activate?
    Hi Shawn

    You don't usually use select/activate objects in vba, as you already know. It's unnecessary and hurts code readability and performance.

    Just to make it clear, however, and since you asked, it's not always the same thing to Select and to Activate.

    In your case since you are just selecting/activating one only object, it will have the same effect using one or the other.

    If you were dealing with a group of objects, however, it would not be the same thing.

    Select: selects a group of objects and activates the first one in the group.

    Activate: if the object is not yet selected, selects it before activating it. If the object is already selected, simply activates it, does not change the selection.

    Example.

    Let's say you select the range("A1:B3"). This causes 6 cells to be selected and the first one, A1, to be the active cell.

    Now you want to keep the selection, but you want the cell B1 to be the active cell. In this case you'll use Activate on B1. After the statement is executed B1 will be the active cell but all the 6 cells will still be selected.

    The same with worksheets. You can select Sheet1 and Sheet2 and then make Sheet2 active while keeping both selected.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Re: Sheet.Activate versus Sheet.Select

    Thanks for the lesson pgc01; this all makes sense to me. I havenít been using VBA for very long and most of what I do starts out as a recorded macro that I tweak. I bought a VBA manual and visit this site quite often to find ways to shorten code and I find the replies from the experts on this site invaluable. I think my next step will be to take a VBA course or two at a local college.

    Shawn

  8. #8
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Re: Sheet.Activate versus Sheet.Select

    Ok, so I've been playing around with this and the code Mike and MrKowz provided works perfectly. When I try to incorporate this between 2 open workbooks, I get a out of range error. Can you use the previously supplied copy/paste code between workbooks? Or, in this case, do I have to copy the date then activate the destination sheet?

    Here's what I tried that didn't work:

    Sub Copy_Data_Without_Selecting()
    Workbooks.Open Filename:= _
    "Q:\AIP\Teams\Estimating\Oracle System\End Use Programs.xlsx" _
    Sheets("End Use Programs").Range("A1:F5000").Copy _
    Destination:=Workbooks("Book3").Sheets("Sheet2").Range("A1")
    End Sub


    Thanks,
    Shawn

  9. #9
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,499

    Default Re: Sheet.Activate versus Sheet.Select

    Perhaps

    Code:
    Sub Copy_Data_Without_Selecting()
    Workbooks.Open Filename:="Q:\AIP\Teams\Estimating\Oracle System\End Use Programs.xlsx"
    Sheets("End Use Programs").Range("A1:F5000").Copy _
    Destination:=Workbooks("Book3.xlsx").Sheets("Sheet2").Range("A1")
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  10. #10
    New Member
    Join Date
    Oct 2009
    Location
    Cleveland, Ohio
    Posts
    29

    Default Re: Sheet.Activate versus Sheet.Select

    I added the .xlsx to Books3 but I still get a Run-time error '9' Subscript Out of Range.

    Shawn

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com