Although there is a difference between activating and selecting a cell, your code shouldn't be doing either. In almost every situation, you don't need to activate or select a cell in order to read or write its formatting and other properties, change its formula or values, cut, copy or paste. The same thing holds true for activating a worksheet, workbook or window. You don't need to do it.

I realize that the macro recorder selects and activates like crazy, but that doesn't make it a good practice. Selecting or activating a cell will cause the screen to blink, slows down your macro enormously, and frustrates the user by moving the cursor all over kingdom come. Download Game Catur Real Chess Di Android. Learn how to avoid doing it. One of the very few exceptions to the good practice of 'never selecting a cell' is when you manipulate the conditional formatting properties of a cell using formula criteria with relative addresses.

The formula in such situations is interpreted relative to the active cell, so you will get unexpectedly wrong results if you don't select the cell you are trying to format first. I use Application.GoTo in the circumstance described by JamesBurger because it can change both worksheet and range in a single statement. For example, I set a 'return address' before opening a workbook or adding a worksheet. Both of those activities will change the active cell and active worksheet. Dbase 7 more. After the macro is done processing, I use Application.GoTo to return to the original active cell.

Dim celHome As Range Set celHome = ActiveCell 'Open a workbook, add a worksheet or other activity that changes the active worksheet Application.GoTo celHome Select all. In the suggested VBA statement, I assumed that the workbook needing the formula was already active.

What is the difference between the VBA code wb.Sheets(1).Cells.Select and wb.Sheets(1).Activate? This tutorial will teach you about the Cells, Ranges and Offset, ActiveCell, Activate and Select,Activating a Cell Within a Selection and will tell you few.

If not, I would preface the statement with a reference to the workbook: Workbooks('no-selection-ee-question.xlsx').Worksheets('Sheet1').Range('D2:D5').Formula = '=B2*C2' Select all What you want to do is to set the Formula property of a Range object. This Range object can be a variable that was previously defined, or it can be a fully qualified reference to a range of cells. By fully qualified, I mean that you make it clear which workbook, worksheet and cells are involved. The initial example, which is how I would do it except when I am copying back and forth between two different workbooks, VBA assumes that the worksheet reference is to the active workbook. Another way to do is using a variable. Suppose that you want to do a number of tasks with worksheet Sheet1. You might set a variable pointing to it: Dim ws As Worksheet Set ws = Worksheets('Sheet1') ws.Range('D2:D5').Formula = '=B2*C2' ws.Name = 'Billings Summary' Select all The power of a variable is exposed when you want to loop through different items in a collection.

Suppose, for example that you wanted to put the valuation formula in every worksheet in the workbook. To do that, you might use code like: Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Range('D2:D5').Formula = '=B2*C2' Next Select all In the preceding loop, the code doesn't care about the names of the worksheets. Download Thx Tone Generator Program.

It doesn't even care how many worksheets there are. Yet the macro recorder would insist that you go through the process of selecting each sheet, then selecting a range before putting the formula in those cells.

Hello Stackoverflowers, I'm trying to use a button, that first goes to another excel file in a specific directory. While performing something, I want to add a row in a sheet the excel file i'm running the button from. To do that, i need to activate a certain row, or cell to use this ActiveCell.EntireRow.Insert but it keeps telling me: activate method of range class failed my last trail was this: Sheet1.Cells(2, 3).Activate ActiveCell.EntireRow.Insert Can anyone tell me how to get this done? I think because i'm in another workbook or something Thanks. This is just a sample code, but it may help you get on your way: Public Sub testIt() Workbooks('Workbook2').Activate ActiveWorkbook.Sheets('Sheet2').Activate ActiveSheet.Range('B3').Select ActiveCell.EntireRow.Insert End Sub I am assuming that you can open the book (called Workbook2 in the example).