VBA: activating/selecting a worksheet/row/cell

Question

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

Try Sheet1.Cells(2,3).EntireRow.Insert. Alternatively, try Application.GoTo Sheet1.Cells(2,3) — mkingston, Oct 04 '12 at 23:13
I couldn't activate the workbook, can please tell me how? i'll try the other solutions for the time being — himura, Oct 04 '12 at 23:16
that worked! i was using this also (old code i tried as well): Sheets("Sheet1").Rows(2).Insert, didn't work too — himura, Oct 04 '12 at 23:20
dim openedWorkbook as WorkBook. set openedWorkbook = workbooks.open("path"). openedWorkbook.activate. — mkingston, Oct 04 '12 at 23:20
@mkingston I've used that to open another file. are you suggesting i do it to my original excel file to return to it? — himura, Oct 04 '12 at 23:22
@himura Just one thing: I think you should use `Cells(2,3).Select` instead of `Cells(2,3).Activate` — Barranka, Oct 04 '12 at 23:27
hmmmm, i'm no expert, but can you tell me why? so i can understand for later :) — himura, Oct 04 '12 at 23:39
@himura I was suggesting you use that code to open/activate your new workbook. You can use Workbooks("filename.xls").Activate to activate any already open workbook. — mkingston, Oct 04 '12 at 23:55
Why are you using activate in the first place? You do realise that you don't actually need to select or activate neither the workbook or the cell, to insert cells, right? — Reafidy, Oct 05 '12 at 01:55
+ 1 @Reafidy for the only one who suggested not to use `.Activate` -@himura: Please see this link: http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select — Siddharth Rout, Oct 05 '12 at 07:23
@Reafidy yes I've realized that. The thing is that all examples i saw in the net to add rows uses activate or select... and since i'm still new to VBA, i couldn't figure out the twist and wanted to go as close as the examples — himura, Oct 05 '12 at 10:37
@himura, I understand. However the correct way is to avoid using select/activate. Only once or twice have I ever found a legitimate reason to use select/activate. Learning the correct way now will save you tons of headaches, just take a look at the second section of Barranka's code if you haven't already. — Reafidy, Oct 05 '12 at 20:03

Barranka · Accepted Answer · 2012-10-07T14:37:34.273

8

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).

I think (but I'm not sure) you can squash all this in a single line of code:

    Workbooks("Workbook2").Sheets("Sheet2").Range("B3").EntireRow.Insert

This way you won't need to activate the workbook (or sheet or cell)... Obviously, the book has to be open.

edited Oct 07 '12 at 14:37

answered Oct 04 '12 at 23:24

Barranka

20,547
13
65
83

1

Notice that you can change `ActiveSheet.Range("B3").Select` with `ActiveSheet.Cells(2,3).Select` – Barranka Oct 04 '12 at 23:26
+ 1 for `Workbooks("Workbook2").Sheets("Sheet2").Range("B3").EntireRow.Insert` – Siddharth Rout Oct 05 '12 at 07:38
Did not work for me. But assuming macro is activated from correct workbook and sheet the following worked fine in Excel 2010. Range(1, 1).Select – vlad Sep 29 '13 at 05:04

VBA: activating/selecting a worksheet/row/cell

1 Answers1