Sheet.Activate versus Sheet.Select

shawnrpg

New Member
Joined
Oct 23, 2009
Messages
31
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
"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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Aaaaahhh, I see; nice. And that eliminates activating/selecting the second sheet like Mike stated.

Thanks to both of you.

Shawn
 
Upvote 0
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.
 
Upvote 0
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.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Shawn
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top