How do I activate a specific workbook and a specific sheet

Question

How do I activate my Other workbook from the Current workbook  I have a current workbook with dumb xls and The other workbook name as Tire xls I have opened the Tire xls from the dumb xls using worksbooks open filename    name of the file  Its getting open but The problem is Im unable to make it work   If I say cells 2 24  value 24 puts these value in the cell of dumb xls but I want it to be done one Tire xls   activesheet cells 2 24  value 24 puts these on Tire xls  But how do i activate the Workbook with the name   I need to open 3 to 4 excel workbooks And perform the operation  How do I activate the specific workbook   I have found this code on google       activeworkbook worksheet  sheetname   activate    but not working      windows  sheetname   activate   people on google suggested not to use   Its not getting activated  I dont know how to make it work  Can anyone tell me How do i activate a specific workbook and a specific sheet of the other workbook    Example  I have niko xls and niko 2 xls opened as workbooks from the dumb xls workbook so totally 3 workbooks and I have to activate the 2nd sheet of niko 2 xls workbook How do I make it  Can anyone explain me the syntax  with these example  Thank you in advance

User · Answer

You have to set a reference to the workbook you're opening. Then you can do anything you want with that workbook by using its reference.

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

Could even set a reference 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 pointed out that .Activate may be superfluous in your case. You don't strictly need to activate a sheet before editing its cells. But, if that's what you want to do, it does no harm to activate -- except for a small hit to performance which should not be noticeable as long as you do it only once or a few times. However, if you activate many times e.g. in a loop, it will slow things down significantly, so activate should be avoided.

User · Answer

You can try this   Workbooks  Tire xls   Activate  ThisWorkbook Sheets  Sheet1   Select Cells 2 24  value 24

User · Answer

The code that worked for me is   ThisWorkbook Sheets  sheetName   Activate

User · Answer

You do not need to activate the sheet  you ll take a huge performance hit for doing so  actually   Since you are declaring an object for the sheet  when you call the method starting with  wb   you are selecting that object  For example  you can jump in between workbooks without activating anything like here   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

User · Answer

try this   Windows  name xls   Activate

User · Answer

Dim Wb As Excel Workbook Set Wb   Workbooks Open file path  Wb Sheets  Sheet1   Cells 2 24  Value   24 Wb Close   To know the sheets name to refer 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

[vba] How do I activate a specific workbook and a specific sheet?

Examples related to vba

Examples related to excel