How to activate a specific book and specific sheet?

Question

How do I activate my other book from the current book? I have a current workbook with dumb.xls and another book title like Tire.xls.I opened Tire.xls from dumb.xls using worksbooks.open filename:= "name of the file" . It becomes open, but the problem is that Im unable to make it work.

If I say that cells(2,24).value=24 puts these values ​​in the dumb.xls cell, but I want this to be done by one Tire.xls.

activesheet.cells(2,24).value=24 puts them in Tire.xls. But how do I activate a book with a name? Do I need to open 3 to 4 Excel workbooks and perform an operation? How to activate a special book

I found this code on google

  activeworkbook.worksheet("sheetname").activate ' but not working windows("sheetname").activate ' people on google suggested not to use 

Not activated. I do not know how to make it work. Can someone tell me how to activate a specific book and a specific sheet of another book?

Example: I have niko.xls and niko_2.xls that were opened as books from the dumb.xls book, so there are only 3 books, and I need to activate the 2nd sheet of the niko_2.xls tutorial. How to do it? Can someone explain the syntax to me with this example? Thank you in advance

Answer 1

You do not need to activate the sheet (in fact, you will get a huge hit in performance). Because you are declaring an object for a worksheet when you call a method starting with "wb". you select this object. For example, you can switch between books without activating anything like this:

 Sub Test() Dim wb1 As Excel.Workbook Set wb1 = Workbooks.Open("C:\Documents and Settings\xxxx\Desktop\test1.xls") Dim wb2 As Excel.Workbook Set wb2 = Workbooks.Open("C:\Documents and Settings\xxxx\Desktop\test2.xls") wb1.Sheets("Sheet1").Cells(1, 1).Value = 24 wb2.Sheets("Sheet1").Cells(1, 1).Value = 24 wb1.Sheets("Sheet1").Cells(2, 1).Value = 54 End Sub 

Answer 2

You must establish a link to the book to be opened. Then you can do whatever you want with this book using its link.

 Dim wkb As Workbook Set wkb = Workbooks.Open("Tire.xls") ' open workbook and set reference! wkb.Sheets("Sheet1").Activate wkb.Sheets("Sheet1").Cells(2, 1).Value = 123 

May even set a link to the sheet, which will make life easier later:

 Dim wkb As Workbook Dim sht As Worksheet Set wkb = Workbooks.Open("Tire.xls") Set sht = wkb.Sheets("Sheet2") sht.Activate sht.Cells(2, 1) = 123 

Others have indicated that .Activate may be redundant in your case. You do not need to activate the sheet before editing its cells. But, if this is what you want to do, it does not harm activation - with the exception of a small hit on performance, which should not be noticeable if you do this only once or several times. However, if you activate many times, for example. in a cycle, it will significantly slow down, so activation should be avoided.

Answer 3

 Dim Wb As Excel.Workbook Set Wb = Workbooks.Open(file_path) Wb.Sheets("Sheet1").Cells(2,24).Value = 24 Wb.Close 

To find out the name of the sheet for the link in Wb.Sheets("sheetname") , you can use the following:

 Dim sht as Worksheet For Each sht In tempWB.Sheets Debug.Print sht.Name Next sht 

Answer 4

Code that worked for me:

 ThisWorkbook.Sheets("sheetName").Activate 

Answer 5

try it

 Windows("name.xls").Activate 

Answer 6

You can try this.

 Workbooks("Tire.xls").Activate ThisWorkbook.Sheets("Sheet1").Select Cells(2,24).value=24