Referencing cells from other workbooks

[cstimart]

I know it's possible to access tabs/cells from other closed workbooks by using a formula.

Range("A1").Formula = "=[Book2]Sheet1!$A$1"

However, is it possible to use that formula by using the worksheet number of the closed workbook, rather than the tab name?

 

[Joe4]

Well, this code would work, but you would need to temporarily open the other workbook to use it (that shouldn't be an issue, you should be able to open and close that workbook in the VBA code).

It uses the sheet index numbers to "capture" the sheet name of the sheet you want.

    Dim mySheetName As String
 
'   Goto source workbook
    Windows("Book1.xls").Activate
    
'   Goto specified sheet and capture the name
    mySheetName = Sheets(1).Name
    
'   Goto destination workbook and enter formula
    Windows("Book2.xls").Activate
    Range("A1").Formula = "=[Book1]" & mySheetName & "!$A$1"